Icon

QA_​VSR_​Datasets

US (State level) + Global (Somecountries State level) Employee Data: STG_EMP_DATA DIM_EMPLOYEEValidations:"'- Check all columns are Populated - Count of all Child Facilities should match in Source --> Staging --> Target - Above record count of Child Faciliities should match in the Facility Mapping Table - Check all Child Facilities has an equivalent Parent Facility - If country='India', Pers Sub Area in CWL <> CWL City, then 'Client Location' has to be mapped - Pers Sub Area, State Name, City Name validation has to be done for that Country - Count of Employees, Count of Customers by Pers Sub Area, by Country has to be validated against the Source File - sampling on USA, India, UK, Singapore can be tested - Number of Projects by Country can also be validated against the source --> staging--> target tables" : VARCHAR varchar String 0VARCHAR varchar String 1VARCHAR varchar String 2NUMERIC numeric Number (double) 3VARCHAR varchar String 4VARCHAR varchar String 5VARCHAR varchar String 6TIMESTAMP datetime Local Date Time 7 CountryStateCityCountStatusLast_UpdateSourceFileCreateDate String 0 APAC Brazil Canada Germany India India Ireland Italy Lithuania Netherlands Portugal Romania UK USA MexicoString 1 Indonesia Sao Paulo BritishColumbia Uttar Pradesh Karnataka Colorado Georgia Illinois Indiana Massachusetts New Jersey New York North Carolina Pennsylvania Oregon Nevada VirginiaString 2 Noida BangaloreNumber (double) 3 1.0 6.0String 4 Positive Cases Active Positive RecoveredString 5 23-Apr-20String 6 HCL- Covid19 Cases-23 April'19.csvLocal Date Time 7 2020-04-24T04:08:11.460 2020-04-24T04:08:11.460 CountryStateCityCountStatusLast_UpdateSourceFileCreateDate HCL- Covid19 Cases-23 April'19.csvNode 2Read the data from Blob csvAzure SQL instancestaging.STG_COVID_GLOBALNode 93staging.STG_COVID_JHU_GLOBAL_TMPNode 96Node 97Node 98Node 99Node 100CHECK CONFIRMEDCHECK DEATHSAzure SQL instancetarget.DIM_EMPLOYEECount(Distinct(Employee ID))Employee Count 163485Employee ID IS NOT NULLEmployee Details (Main)Node 119Node 120Node 122Node 123TARGET.DIM_FACILITYTARGET.FACILITY_MAPTARGET.DIM_FACILITYTARGET.FACILITY_MAPNode 128Node 13053000177 (sample check)Node 133Distinct Counts of Employees & Child Facilities with no P_FACILITY name/codesP_FACILITY_NAME without P_FACILITY_CODEAdvisory AlertsNode 137Node 138Node 139staging.STG_FACILITY_EMPCUSTstaging.STG_FACILITY_EMPCUST (main)Employee ID IS NOT NULLNode 143staging.FACILITYAzure SQL instanceNode 146 Azure Blob StoreFile Picker Azure Blob StoreConnection File Reader Microsoft SQLServer Connector DB Table Selector DB Reader DB Table Selector DB Reader Joiner Date&Time Shift Date&Time to String Date&Time to String Rule Engine Rule Engine Microsoft SQLServer Connector KNFileBalanceCheck DB Table Selector DB GroupBy DB Reader DB Row Filter DB Reader Rule-basedRow Filter GroupBy Number To String File Reader DB Table Selector DB Table Selector DB Reader DB Reader Reference RowSplitter Reference RowSplitter Rule-basedRow Filter GroupBy Column Rename GroupBy DB Table Selector DB Reader GroupBy Rule Engine DB Table Selector DB Reader DB Row Filter Reference RowSplitter DB Table Selector Microsoft SQLServer Connector DB Reader US (State level) + Global (Somecountries State level) Employee Data: STG_EMP_DATA DIM_EMPLOYEEValidations:"'- Check all columns are Populated - Count of all Child Facilities should match in Source --> Staging --> Target - Above record count of Child Faciliities should match in the Facility Mapping Table - Check all Child Facilities has an equivalent Parent Facility - If country='India', Pers Sub Area in CWL <> CWL City, then 'Client Location' has to be mapped - Pers Sub Area, State Name, City Name validation has to be done for that Country - Count of Employees, Count of Customers by Pers Sub Area, by Country has to be validated against the Source File - sampling on USA, India, UK, Singapore can be tested - Number of Projects by Country can also be validated against the source --> staging--> target tables" : VARCHAR varchar String 0VARCHAR varchar String 1VARCHAR varchar String 2NUMERIC numeric Number (double) 3VARCHAR varchar String 4VARCHAR varchar String 5VARCHAR varchar String 6TIMESTAMP datetime Local Date Time 7 CountryStateCityCountStatusLast_UpdateSourceFileCreateDate String 0 APAC Brazil Canada Germany India India Ireland Italy Lithuania Netherlands Portugal Romania UK USA MexicoString 1 Indonesia Sao Paulo BritishColumbia Uttar Pradesh Karnataka Colorado Georgia Illinois Indiana Massachusetts New Jersey New York North Carolina Pennsylvania Oregon Nevada VirginiaString 2 Noida BangaloreNumber (double) 3 1.0 6.0String 4 Positive Cases Active Positive RecoveredString 5 23-Apr-20String 6 HCL- Covid19 Cases-23 April'19.csvLocal Date Time 7 2020-04-24T04:08:11.460 2020-04-24T04:08:11.460 CountryStateCityCountStatusLast_UpdateSourceFileCreateDate HCL- Covid19 Cases-23 April'19.csvNode 2Read the data from Blob csvAzure SQL instancestaging.STG_COVID_GLOBALNode 93staging.STG_COVID_JHU_GLOBAL_TMPNode 96Node 97Node 98Node 99Node 100CHECK CONFIRMEDCHECK DEATHSAzure SQL instancetarget.DIM_EMPLOYEECount(Distinct(Employee ID))Employee Count 163485Employee ID IS NOT NULLEmployee Details (Main)Node 119Node 120Node 122Node 123TARGET.DIM_FACILITYTARGET.FACILITY_MAPTARGET.DIM_FACILITYTARGET.FACILITY_MAPNode 128Node 13053000177 (sample check)Node 133Distinct Counts of Employees & Child Facilities with no P_FACILITY name/codesP_FACILITY_NAME without P_FACILITY_CODEAdvisory AlertsNode 137Node 138Node 139staging.STG_FACILITY_EMPCUSTstaging.STG_FACILITY_EMPCUST (main)Employee ID IS NOT NULLNode 143staging.FACILITYAzure SQL instanceNode 146Azure Blob StoreFile Picker Azure Blob StoreConnection File Reader Microsoft SQLServer Connector DB Table Selector DB Reader DB Table Selector DB Reader Joiner Date&Time Shift Date&Time to String Date&Time to String Rule Engine Rule Engine Microsoft SQLServer Connector KNFileBalanceCheck DB Table Selector DB GroupBy DB Reader DB Row Filter DB Reader Rule-basedRow Filter GroupBy Number To String File Reader DB Table Selector DB Table Selector DB Reader DB Reader Reference RowSplitter Reference RowSplitter Rule-basedRow Filter GroupBy Column Rename GroupBy DB Table Selector DB Reader GroupBy Rule Engine DB Table Selector DB Reader DB Row Filter Reference RowSplitter DB Table Selector Microsoft SQLServer Connector DB Reader

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: parth
Created at: 2020-04-30
On NodePit since: 2021-08-31
Last update: 2024-11-18
Created with KNIME version: v4.0.2

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!