topic Loop Script help in QlikView

Loop Script help

Fri, 08 Apr 2016 06:04:10 GMT

I am quite new in development in QlikView scripting and I would like to ask for help.

My original data consist of 3 columns: Date, Part_old and Part_new. When there is just one change there is no problem for me to write script. Problem is when, like you see in example a part 'aaaa' is replaced by 'bbbb' on January 1st and afterwards the part 'bbbb' to 'eeee'. Below you can see the original data structure and the desired new layout.

I thought about do while statement, but I am not able to declare variable which will take the value from column C, check the whole column B to find whether there is more replacements of part and write it in a way I described above.

Thanks for help

Michal

Re: Loop Script help

sunny_talwar — Fri, 08 Apr 2016 16:11:35 GMT

I don't really understand what you are trying to do here. Can you may be elaborate a little more?

Re: Loop Script help

swuehl — Fri, 08 Apr 2016 16:19:59 GMT

Shouldn't the third line / 'Partold_original' value be 'cccc'?

Re: Loop Script help

Fri, 08 Apr 2016 16:58:48 GMT

Yes swuehl‌, you are right. It is 'cccc'

The idea is that original table is sorted by date in ascending order (older to younger) when Part_old was subsituted by Part_new, but there can be 1 to n substitutes. I would like to see those substitutes line by line. It means that part_new can be in another line shown like part_old substituted by another part. The aim is to create script to check it and organize it in a way shown above.

Column D will just show the first original part and doesn't take into consideration no of substitutes.

Re: Loop Script help

swuehl — Fri, 08 Apr 2016 23:07:10 GMT

Here is another idea. Not really sure if this is applicable to your setting, let's see:


Hierarchy:
Hierarchy (Part_new,Part_old, NodeName,ParentName,NodeName,Path,'/',Depth)
LOAD *,  Part_new as NodeName, RowNo() as RowID;
LOAD Date, RecID, If(IterNo()=2,Null(),Part_old) as Part_old, If (IterNo()=2,Part_old,Part_new) as Part_new
While iterno() <= Num;
LOAD *, Recno() as RecID, If(not Exists(Part_new,Part_old),2,1) as Num INLINE [
Date, Part_old, Part_new
01.01.2016,aaaa,bbbb
01.01.2016,cccc,ddddd
03.02.2016,bbbb,eeee
];


RESULT:
LOAD *, Subfield(Path,'/',1) as Part_old_orig
Resident Hierarchy
WHERE Len(trim(Part_old));


DROP TABLE Hierarchy;

Date	Part_old	Part_new	Part_old_orig	Path
01.01.2016	aaaa	bbbb	aaaa	aaaa/bbbb
03.02.2016	bbbb	eeee	aaaa	aaaa/bbbb/eeee
01.01.2016	cccc	ddddd	cccc	cccc/ddddd

Re: Loop Script help

Mon, 11 Apr 2016 12:06:05 GMT

Great!! Thank you very much.

Just noticed one problem. I try to apply order by statement but without success because sometimes the path is going form older date to younger, sometimes vice versa, any suggestions to which line apply the condition??

Re: Loop Script help

swuehl — Mon, 11 Apr 2016 23:19:55 GMT

Could you upload a small sample of your input data and your current script?