I wonder in which conditions does FirstSortedValue delivers a NULL value?

[draghici1109]

Hello everybody!

I am rookie waiting for the next Qlik Scripting Training next week. However I have to solve following problem: I use FirstSortedValue with INLINE Table. The same algorythm and the same field names I use the second time by reading the fields as paramater for FirstSortedValue from a qvd file on the disk.

Challenge: 1st time I use FirstSortedValue delivers the right results. 2nd time I run it with data loaded from a qdf file deliver me NULL values as a result of the execution of the FirstSortedValue. I do not see the difference and have no explanation for the NULL values.

I wonder in which conditions does FirstSortedValue delivers a NULL value?

Here my code:

GIMEL:
LOAD * INLINE
[
TABNR, TABNRuTABSEQNR, TABSEQNR
HP2-----200A00000000000000018801, HP2-----200A00000000000000018801|00004, 00004
HP2-----200A00000000000000018801, HP2-----200A00000000000000018801|00005, 00005
HP2-----200A00000000000000018802, HP2-----200A00000000000000018802|00008, 00008
HP2-----200A00000000000000018802, HP2-----200A00000000000000018802|00007, 00007
HP2-----200A00000000000000018833, HP2-----200A00000000000000018833|00004, 00004
HP2-----200A00000000000000018833, HP2-----200A00000000000000018833|00010, 00010
HP2-----200A00000000000000018833, HP2-----200A00000000000000018833|00005, 00005
HP2-----200A00000000000000018833, HP2-----200A00000000000000018833|00006, 00006
HP2-----200A00000000000000018833, HP2-----200A00000000000000018833|00009, 00009
HP2-----200A00000000000000018833, HP2-----200A00000000000000018833|00003, 00003
HP2-----200A00000000000000018833, HP2-----200A00000000000000018833|00002, 00002
HP2-----200A00000000000000018833, HP2-----200A00000000000000018833|00001, 00001
];

DALET:
LOAD
TABNR,
FirstSortedValue(TABNRuTABSEQNR, TABSEQNR) AS TABNRuTABSEQNR
Resident GIMEL
GROUP BY TABNR;

STORE [DALET] INTO [..\DATA_20\DALET.qvd](qvd);
Drop Table [DALET], [GIMEL];

// Results are here:

// The same logic with the same field name is now implemented by loading data not with INLINE but rather
// from a qvd file named VorbereitungHKSTL.qvd

HE:
//TABNRuTABSEQNR will be later used as key for joining with the rest of the fields of VorbereitungHKSTL
LOAD
TABNR,
TABNR & '|' & TABSEQNR AS TABNRuTABSEQNR,
TABSEQNR

FROM [..\DATA_20\VorbereitungHKSTL.qvd](qvd);
STORE [HE] INTO [..\DATA_20\HE.qvd](qvd);
Drop Table [HE];

// Sample Data VorbereitungHKSTL:

VAV:
LOAD
TABNR,
FirstSortedValue(TABNRuTABSEQNR, TABSEQNR) AS TABNRuTABSEQNR
FROM [..\DATA_20\HE.qvd](qvd)
GROUP BY TABNR;
STORE [VAV] INTO [..\DATA_20\VAV.qvd](qvd);
Drop Table [VAV];

// VAV.qvd sample, the second field contains NULL

EXIT SCRIPT;

[marcus_sommer · Accepted solution]

Firstsortedvalue() returns NULL if there are multiple different return-values for the appropriate lookup-value available. It's a bit similar like the function only().

Your noticed differences are probably caused from the differences between both used datasets. The inline-table has just a few records and so the likely that a lookup-value has multiple return-values is rather small but if there is a bigger dataset like in the qvd the likely increased.

This means firstsortedvalue() is only in very specific scenarios usable. In your case you may rather use max() / maxstring() or using different approaches like sorting the data and flagging the appropriate values with interrecord-functions like previous() or peek() or also a mapping/joining based on maxstring() or similar to flag/filter the dataset.

- Marcus

[draghici1109]

Marcus,

thank you for your extended answer!

I worked on it and used some flags as you suggest.

Do you have a hint for me where I can deepen your suggestion: "flagging the appropriate values with interrecord-functions like previous() or peek()"?

- Alexandru

[marcus_sommer]

Within a sorted resident-load - something like:

...
order by Field1, Field2 desc;

and then you may use an calculation like this one:

if(Field1 <> previous(Field1), 1, rangesum(peek('Counter'), 1)) as Counter

to get a running counter-field in regard to your conditions.

This means you need to assure that the load is sorted properly and that you query the values from the current record against the previous ones. Depending on the number of relevant fields and/or the complexity of the requirement you might need more as a single if-loop else nested ones and also several loads if the sorting needs to be either ascending as well as descending - which may exclude each other within a single load. 

- Marcus