2 Replies Latest reply: Jan 26, 2017 3:54 AM by Marcus Sommer RSS

    Validation Checks in Load Script

    Abhay Sudhakaran

      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".

        • Re: Validation Checks in Load Script
          Girirajsinh Vaghela

          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.

          • Re: Validation Checks in Load 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