topic Re: Code Understanding in QlikView

Code Understanding

abhaysingh — Fri, 12 Jun 2015 06:36:59 GMT

Hi All,

can any one help me understand this code

LOAD

RowNo() AS Row,

Process,

If(Process='TOTAL',0,

If(IterNo()= 2,

If(IsNull(Peek(WaterFallBar,-2)),0,Peek(WaterFallBar,-2)+Peek(Cost,-3)))) AS WaterFallBar,

If (Process <> 'TOTAL',If(IterNo()=1,Cost),Peek(WaterFallBar,-2)+Peek(Cost,-3)) AS Cost,

If(IterNo() = 1, 'DATA' , 'OFFSET') AS ColorCode,

If(IterNo() = 1, 2 , 1 ) AS DimCol WHILE IterNo() < 3;

LOAD

INLINE [

Process, Cost

Code, 6

Customisations, 3

Design, 2

Marketing, 4

Sales, 1

TOTAL,

];

[ColorOffsets]:

LOAD RecNo() AS ColorOffset AUTOGENERATE 1000;

Re: Code Understanding

jyothish8807 — Fri, 12 Jun 2015 06:54:45 GMT

Hi Abhay,

Over here there are few this happening.

1. WaterFallBar: if Process ='TOTAL' it will show 0 and for Next value it will be 0 again and keeps on adding for other values.0,6,9,11 and so on

2.Cost= if Process is not equal to 'Total' it will show its actual cost for all process and for TOTAL you will get the grand total.

3.ColorCode: For first set it will show OFFSET and for other set it will show DATA.

4.DimCol:For first set it will show 1 and for other set it will show 2.

Regards

Re: Code Understanding

Ralf-Narfeldt — Fri, 12 Jun 2015 06:59:36 GMT

I guess your main question is about the preceding LOAD.

WHILE IterNo() < 3

This makes the preceding LOAD read each record in the inline loaded table twice, so it will create two records for every original record.

If(IterNo()= 2,

If(IsNull(Peek(WaterFallBar,-2)),0,Peek(WaterFallBar,-2)+Peek(Cost,-3)))) AS WaterFallBar,

The second time a record is read (IterNo()= 2) it peeks into already loaded rows 2 or 3 rows up and calculates a value

If(IterNo() = 1, 'DATA' , 'OFFSET') AS ColorCode,

the first time a record is read ColorCode is set to DATA, the second time it will be OFFSET

I think you can work out the others, as they are similar, using IterNo() to see which iteration of the record, and Peek to check values already loaded