topic Re: LOAD script - Assign field values to a variable in QlikView

LOAD script - Assign field values to a variable

phoenix — Thu, 31 Jan 2019 19:51:24 GMT

What is the easiest way to get to this without having to write several load steps and IF conditions for each code?

CODE_1_DATE is the date CODE_1 is placed, similarly for other codes

If an Account has two same codes, like 789 below, it has 120 in code_1 and code_2, I would need the 120 that is places latest. so it would be 1/9/2019

In the actual data I have as many as 12-15 codes.

HAVE:

ACCOUNT	CODE_1	CODE_2	CODE_3	code_4	CODE_1_DATE	CODE_2_DATE	CODE_3_DATE	CODE_4_DATE
123	120	121	122		1/2/2019	1/3/2019	1/4/2019
456	120	121	122	123	1/20/2019	1/3/2019	1/5/2019	1/8/2019
789	120	120	122	121	1/8/2019	1/9/2019	1/10/2019	1/15/2019

WANT:

ACCOUNT	CODE_120	CODE_120_DATE	CODE_121	CODE_121_DATE	CODE_122	CODE_122_DATE	CODE_123	CODE_123_DATE
123	Y	1/2/2019	Y	1/3/2019	Y	1/4/2019
456	Y	1/20/2019	Y	1/3/2019	Y	1/5/2019	Y	1/8/2019
789	Y	1/9/2019	Y	1/15/2019	Y	1/10/2019

Re: LOAD script - Assign field values to a variable

marcus_sommer — Fri, 01 Feb 2019 08:09:40 GMT

I doubt that I would try to solve this and other tasks with your displayed data-structure else I would transform it with two joined crosstable-loads - means to load code and code-date separate and joining them together:

The-Crosstable-Load

After it I'm not sure that I would flag everything within the datamodel else I could imagine that you could fetch your needed values with firstsortedvalue() directly within the UI. If it should really happens within the script you might also use min/max/firstsortedvalue in separate loadings and just keeping them as associated tables or maybe re-joining them to the resident table.

An alternatively to the aggregation approach might be to use interrecord-functions within a sorted load to access and compare the previous loaded records:

Peek-or-Previous

- Marcus