Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

sudhakaran_abha
Contributor

Validation Checks in Load Script

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:

SectorRevenue
Blue100
Red150
Orange220
White120

Mapping Table:

SectorMapZone
BlueZone 1
RedZone 1
OrangeZone 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".

2 Replies
girirajsinh
Contributor III

Re: Validation Checks in Load Script

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.

MVP & Luminary
MVP & Luminary

Re: Validation Checks in Load Script

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