while loop before preceding load

[Anonymous]

Hi Qlikview Gurus,

Can somebody please explain me the below code in qlikview?

Doubts:-

1.My understanding is third load fetches the data from spreadsheet which in turn passes to the preceding load.

why should the  "while" loop should be at the top instead it should be down as to fetch the data from the normal load and preceding load.

2.Agreed if all values are passed,what if i write one more while loop above it will it pass?

3.how many levels does it pass?

Kindly help me understand this logic.

LOAD YearMonth,

  DaysInMonth,

  today(),

  Date( YearMonth+iterno()-1) as LinkDate

While iterno() <= DaysInMonth;

LOAD *,//PRECEDING LOAD

  If(YearMonth<Monthstart(Today()), Day(Monthend(YearMonth)), Day(Today()-1)) as DaysInMonth;

LOAD Date#(YearMonth,'YYYYMM') as YearMonth//Normal Load from spreadsheet

FROM

...

(ooxml, embedded labels, table is Sheet1)

where isNUM(YearMonth);

Regards,

Kushal

[Gysbert_Wassenaar · Accepted solution]

1. Technically the while loop can be in the bottom load too. But then you'd have to use something like

While IterNo() <= If(Date#(YearMonth,'YYYYMM') <Monthstart(Today()), Day(Monthend(Date#(YearMonth,'YYYYMM') )), Day(Today()-1))

The preceding loads are just for convenience. You create a field and in the preceding load you use the newly created field in another calculation.

2. I don't understand the question. If you add another preceding load with an additional while construct then you'll generate more records out of the records from the load below it.

3. As many as the number of preceding loads you use.

[Anonymous]

Thank you Gysbert!!! for making me understand it.

I would be grateful to you if u can throw ur expert advise on this and clear my two points.

One point I failed to understand is why execution happens from bottom to top.Being a beginner and knowing some scripting language, if the variables are not initialized from top to  bottom approach(readability) it throws the error before proceeding.So then why loop load(construct) is not executed at first instance and is being passed to next load? then after loading it comes back to while construct to gives the dates of each months per the logic i.e. bottom to top.

Adding to it,the output should be same if the while construct is at the bottom or top as the values are passed the same way and it has to be iterated no of times to give the same output but it is not.

Regards,

Kushal

[Gysbert_Wassenaar]

One point I failed to understand is why execution happens from bottom to top.

Because Qlik designed it that way: Preceding Load

[Peter_Cammaert]

When I look at your way of spacing the different statements, it may help to keep the preceding loads and original load closely together. One way to better understand and handle this is to consider a preceding load and the first load that comes after as one nested statement. A preceding load cannot stand on its own.

SQL allows for nesting of SELECT statements whereby the outer SELECT uses the results of the inner SELECT as source/control data. In QlikView script you cannot nest LOAD statements and the preceding load is as far as you can syntactically get to a nested load.

Peter

[Anonymous]

Thank you peter!!! for ur information.

[qlikviewaf]

so we can have sequence of FLY only if the stariting is different than "Consignment"