topic Problem with a double load loop in QlikView

Problem with a double load loop

max_potass — Wed, 30 May 2018 16:42:27 GMT

Hi everyone,

I have a Problem with a double Load Loop. It is quite complicated to explain but here is the script:

KAM_Temp1:
LOAD
 ows_ID as KAM_ID,


    Replace(ows_TestoKamStatusHistory,' ->  ',' -> ') as KAM_Status_Change_History   


FROM [https://Something] (XmlSimple, Table is [xml/data/row]);


Store 'KAM_Temp1' into  \\qlikpv01\BusinessUnit\QlikTables\KAM_Temp1.qvd (qvd);


For i=1 to 9


Left Join Load
 KAM_ID,
 If(SubStringCount(KAM_Status_Change_History,'.')/3=0 OR SubStringCount(KAM_Status_Change_History,'.')/3 < $(i),Null(),
 If($(i)=1,
 Left(Left(KAM_Status_Change_History,Index(KAM_Status_Change_History,'  ')),10),
 If(SubStringCount(KAM_Status_Change_History,'.')/3 = $(i),
 Mid(KAM_Status_Change_History,FindOneOf(KAM_Status_Change_History,'.',1+3*($(i)-1))-2,10),
 Left(TextBetween(KAM_Status_Change_History,'  ','  ',$(i)-1),10)))) as KAM_Status_Change_$(i),
 If(SubStringCount(KAM_Status_Change_History,'.')/3=0 OR SubStringCount(KAM_Status_Change_History,'.')/3 < $(i),Null(),
 Mid(KAM_Status_Change_History,Index(KAM_Status_Change_History,' -> ',$(i))+4,FindOneOf(KAM_Status_Change_History,'.',(3*$(i)))-Index(KAM_Status_Change_History,' -> ',$(i))-4)) as KAM_Status_Change_$(i)_to
 
FROM \\qlikpv01\BusinessUnit\QlikTables\KAM_Temp1.qvd (qvd);


Next i;


Store 'KAM_Temp1' into  \\qlikpv01\BusinessUnit\QlikTables\KAM_Temp2.qvd (qvd);


For i=1 to 9
For j=2 to 10


Left Join Load
 KAM_ID,
 If(KAM_Status_Change_$(i)_to = $(j), KAM_Status_Change_$(i)) as KAM_Status_$(j)_changed_to_date
FROM \\qlikpv01\BusinessUnit\QlikTables\KAM_Temp2.qvd (qvd);
Next j;
Next i;

I do this, as I need to extract a date out of string. Which does work, so i get the expected result until line 27. Then I need to transform the Information in other fields with the fieldname containing the actual status. So I am doing a double loop. I first check each field (1 to 9) I created earlier and check if the content status changed to the status i am currently looking for.

So for the first iteration I look in Field KAM_Status_Change_1_to if there is the content 2. If yes, I want that KAM_Status_Change_1 to be loaded in field KAM_Status_2_changed_to_date. Next Iteration should be looking in KAM_Status_Change_1_to for value 3 and if that's the content I want to load KAM_Status_Change_1 to be loaded in KAM_Status_3_changed_to_date. Until it reaches 10 for the content of KAM_Status_Change_1_to . After That I proceed with KAM_Status_Change_2_to and again look for all values from 2 to 10.

I did not found a logical error in my loop and I do not get an error.

My Problem is, that in the final table I only get the first value that exists for the field KAM_Status_XX_changed_to_date and after that I do not get any values for these fields. So In one line there is only one value for the fields KAM_Status_XX_changed_to_date, even though there should be.

Can someone maybe check my script and tell me, what I did wrong? I cannot find anything wrong with it from a logical point of view, but maybe I am blind or there is some qlikview related issue here...

Best Regards and Thanks

Max

Re: Problem with a double load loop

marcus_sommer — Thu, 31 May 2018 07:15:50 GMT

Without diving deeply into your approach and looking on the input and output and where it is different from the expection it will be quite difficult to say what could be wrong. When you say that you couldn't detect a logical error it could also be that the content of your data respectively the data-quality is not so consistently like you think and it might need some further cleaning and/or more logic to handle all possible cases.

Beside this I'm not sure that your approach is really suitable because I have the feeling that it is more complicated as necessary and you are creating a crosstable which had rather seldom advantages against a "normal" table-structure.

Therefore I suggest to rethink your approach and if it coudn't be solved more easily within a "data-stream" load by using subfield() and/or while-loops to split your field-values into several parts and applying on them your various checkings.

Here an example from a quite different task but it showed a bit the direction of what I'm speaking above by using a preceeding-load in combination with a while-loop:

Re: for each loop

At least I think it could give you ideas that your task might be solvable in quite different manner as the "classical" for-loops.

- Marcus

Re: Problem with a double load loop

max_potass — Thu, 31 May 2018 08:44:32 GMT

Thank you. I will look into it. It was just the only way I came up with.

The Data is perfect... I looked at all of it. (Not that much Data actually)

Re: Problem with a double load loop

max_potass — Tue, 05 Jun 2018 12:30:59 GMT

I figured it out myself.

The Problem was the "Left Join Load" in the Loop, as it does not override the previous entry for that field. Therefore only the first value for each field was properly fetched...

I created a basic concetanate table and reloaded that with the max values. That worked fine.

For j=1 to 10
KAM_Status_Dates_Temp:
Load * Inline
 [KAM_ID, $(KAM_Status_$(j)_changed_to_date)
 $(Null()),$(Null())];


Next j;


For i=1 to 10
For j=1 to 10
KAM_Status_Dates_Temp:
Concatenate
Load
 KAM_ID,
 If(KAM_Status_Change_$(i)_to = $(j), KAM_Status_Change_$(i)) as KAM_Status_$(j)_changed_to_date
FROM \\qlikpv01\BusinessUnit\QlikTables\KAM_Temp2.qvd (qvd) where Not KAM_Error='x';
Next j;
Next i;


Store 'KAM_Status_Dates_Temp' into  \\qlikpv01\BusinessUnit\QlikTables\KAM_Status_Dates_Temp.qvd (qvd);


Drop Table KAM_Status_Dates_Temp;


Load 
 KAM_ID,
 Date(Max(KAM_Status_1_changed_to_date)) as KAM_Status_1_changed_to_date,
 Date(Max(KAM_Status_2_changed_to_date)) as KAM_Status_2_changed_to_date,
 Date(Max(KAM_Status_3_changed_to_date)) as KAM_Status_3_changed_to_date,
 Date(Max(KAM_Status_4_changed_to_date)) as KAM_Status_4_changed_to_date,
 Date(Max(KAM_Status_5_changed_to_date)) as KAM_Status_5_changed_to_date,
 Date(Max(KAM_Status_6_changed_to_date)) as KAM_Status_6_changed_to_date,
 Date(Max(KAM_Status_7_changed_to_date)) as KAM_Status_7_changed_to_date,
 Date(Max(KAM_Status_8_changed_to_date)) as KAM_Status_8_changed_to_date,
 Date(Max(KAM_Status_9_changed_to_date)) as KAM_Status_9_changed_to_date,
 Date(Max(KAM_Status_10_changed_to_date)) as KAM_Status_10_changed_to_date


From \\qlikpv01\BusinessUnit\QlikTables\KAM_Status_Dates_Temp.qvd (qvd) Group By KAM_ID;