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);
Store 'KAM_Temp1' into \\qlikpv01\BusinessUnit\QlikTables\KAM_Temp2.qvd (qvd);
For i=1 to 9
For j=2 to 10
Left Join Load
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);
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...
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: