script snippet explanation

[joioan]

Hi all! Can you help me understand this code bit?

Load Key,
     date(SubField(Key,'|',1)) as CanonicalDate,
     'IssueDate' as DateType;
Load FieldValue('Key',RecNo()) as Key
AutoGenerate FieldValueCount('Key');

Going upwards; The first LOAD statement  ( Load FieldValue... Autogenerate...)  loads Key from AutoGenerate clause.

Now the second (topmost) Load is a preceding load to the other one. I get it.

But this doesn't mean that the Load FieldValue... Autogenerate.. bit should work on its own ?

I found out it doesn't when trying to break down the code.

Why is that?

Thank you in advance 🙂

[Lech_Miszkiewicz]

Hi @joioan 

That part of script will work only if you load earlier 'Key' field to your data model as functions "FieldValue" and "FieldValueCount" are system functions which are tapping into Qlik Engine and derive information about previously loaded field which in this case is 'Key' field.

You need to have at least that field loaded before you can use: 

Load FieldValue('Key',RecNo()) as Key
AutoGenerate FieldValueCount('Key');

[joioan]

@Lech_Miszkiewicz  Thank you so much, I see. So, please, for me to gain better clarity on this script, correct me if I got it wrong:

FieldValueCount('Key') counts the incidences of different Keys,for example for Key values 1, 1, 2, 3,3,3

the outcome would be  2,1,3 respectively. And line LOAD FieldValue('Key',Recno()) as Key, assigns to recno() 1 the value 2 to Key, to recno() 2 the value 1 to Key, to recno() 3 the value 3 to Key and in my case to recno() 4 it would be a null value.

How does this sound?

[Lech_Miszkiewicz]

No, it is not that.

I suggest you study help document to understand those functions: 

• https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/ChartFunctions/In....
• https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/ChartFunctions/In...

FieldValue - returns a value of field for particular index of that field and FieldValueCount returns number of distinct values in field. Those 2 functions refer to how Qlik engine is storing each value of the field as disitnct record with index assigned to it, so by knowing total number of distinct values in field which is returned by FieldValueCount you are iterating through each index and obtaining its symbol value.

Your script is basicly optimized version of loading distinct values of 'Key' field by using those 2 functions. 

If this is not clear then I would suggest to study how Qlik engine works. https://community.qlik.com/t5/Design/Symbol-Tables-and-Bit-Stuffed-Pointers/ba-p/1475369

cheers