topic Re: Synthetic Keys in QlikView

Synthetic Keys

Thu, 13 Oct 2016 16:59:06 GMT

I have a script that looks something like below

sub Transform(vMinDay, vMaxDay)

FOR i = vMinDay to vMaxDay

INDEX:

LOAD

IF(LEN(TRIM(C)) > 0, C, '<BLANK>') AS [REPORTING C],

FROM...$(i)

LEFT JOIN(INDEX)

LOAD,

FROM...$(i)

LEFT JOIN(INDEX)

LOAD

FROM...$(i)

END SUB

CALL Transform(1,2)

I'm getting synthetic keys for every field that I create in the first load of INDEX. I bolded the fields I get synthetic keys for. For example, I get synthetic keys for the following fields: A, B, [REPORTING C] and D.

Thoughts anyone?

Re: Synthetic Keys

MarcoWedel — Thu, 13 Oct 2016 22:38:20 GMT

after the joins your INDEX table has different fields from the initial load, thus subsequent loads will not be autoconcatenated to the INDEX table.

Try with Concatenate(INDEX) preceding the INDEX LOAD.

You might have to define it in a variable after the first load to avoid an error.

Edit: would not work

hope this helps

regards

Marco

Re: Synthetic Keys

frank_dehner — Mon, 17 Oct 2016 07:27:42 GMT

The problem is the loop! In first run the join will work as expected by key-field "A". In the next run the other fields are present too and join will take them also as key. So your join result is different from (correct) first run.

One possible workaround is to load the partial data to be joined into temp tables inside the loop and join them outside the loop. Try something like this:

(By the way: As you can see I prefer transparency for the kind of load to ensure what happens really.)

sub Transform(vMinDay, vMaxDay)

INDEX:

NoConcatenate LOAD * INLINE [A, B, 'REPORTING C', D];

_tmp_EFJ:

NoConcatenate LOAD * INLINE [A, E, F, J];

_tmp_KLM:

NoConcatenate LOAD * INLINE [A, K, L, M];

FOR i = vMinDay to vMaxDay

Concatenate(INDEX)

LOAD

IF(LEN(TRIM(C)) > 0, C, '<BLANK>') AS [REPORTING C],

FROM...$(i)

Concatenate(_tmp_EFJ)

LOAD

FROM...$(i)

Concatenate(_tmp_KLM)

LOAD

FROM...$(i)

JOIN(INDEX) LOAD

//key

//new values

E,F,J

Resident _tmp_EFJ;

JOIN(INDEX) LOAD

//key

//new values

K,L,M

Resident _tmp_KLM;

Drop Tables _tmp_KLM, _tmp_EFJ;

END SUB

CALL Transform(1,2)

Re: Synthetic Keys

Mon, 17 Oct 2016 18:58:18 GMT

Thanks, that makes so much more sense!