Validation Checks in Load Script

[Anonymous]

Hi There,

I am trying to put some validation checks in my Qlik Sense Load Script. The idea is to be able to compare distinct value counts between a dimension from a monthly file and a mapping table (which is maintained manually). For example:

Fact File:

Sector	Revenue
Blue	100
Red	150
Orange	220
White	120

Mapping Table:

SectorMap	Zone
Blue	Zone 1
Red	Zone 1
Orange	Zone 2

In the above example, the mapping file is missing an entry for "White" sector, and hence would show a blank / Null in "Zone"

I want to compare the distinct counts in both these file during the loading process, and throw an error is there is a count mismatch.

Example:

Let vFactCount = Count (Distinct Sector);

Let vMappingCount = Count (Distinct SectorMap);

If '$(vFactCount )' <> '$(vMappingCount )' then

Trace 'ERROR: Count Mismatch:  Fact Count - ' & '$(vFactCount )' & 'and Mapping Count - ' & '$(vMappingCount )' ;

Else

Trace "Validation Check Complete";

End if

But this does not work as intended, It does not capture any count values and gives the output "Validation Check Complete".

[girirajsinh]

You need to use peek first to get value into variables. Then compare variable in if

TableB:
LOAD
Count(DISTINCT Sector) as fc
Resident FactFileTable;

Let vFactCount = Peek('fc',0,TableB);

Please do the same for both your variables and then compare. AS of now both having NULL I guess as per your current script.

[marcus_sommer]

I think you could use the following:

Let vFactCount = fieldvaluecount('Sector');

Let vMappingCount = fieldvaluecount('SectorMap');

If '$(vFactCount )' <> '$(vMappingCount )' then

Trace 'ERROR: Count Mismatch:  Fact Count - ' & '$(vFactCount )' & 'and Mapping Count - ' & '$(vMappingCount )' ;

Else

Trace "Validation Check Complete";

End if

whereby you will need to load the mapping-data twice - one time normal to be able to access the table/fields and then a second time as mapping-table which is in general unaccessable unless by using mapping functions/statements.

- Marcus

[alkoni]

You can see attached code below :

[alkoni]

FactFile:

LOAD

[Sector],

[Revenue]

FROM [lib://AttachedFiles/FactFile.xlsx]

(ooxml, embedded labels, table is Sheet1);

MappingTable:

LOAD

[SectorMap],

[Zone]

FROM [lib://AttachedFiles/MappingTable.xlsx]

(ooxml, embedded labels, table is Sheet1);

TableB:

LOAD

Count(DISTINCT Sector) as ff

Resident FactFile;

Let vFactCount = Peek('ff',0,TableB);

TableC:

LOAD

Count (Distinct SectorMap) as mf

Resident MappingTable;

Let vMappingCount = Peek('mf',0,TableC);

If $(vFactCount) <> $(vMappingCount) then

Trace 'ERROR: Count Mismatch: Fact Count': $(vFactCount)  'Mapping Count': $(vMappingCount)  ;

Elseif

Trace 'Validation Check Complete';

Endif

Exit Script;