topic Loop not executing in QlikView

Loop not executing

pavandbs — Sun, 24 Jul 2016 15:31:38 GMT

Hi All,

I am trying to get achieve below

Source:

Transaction Date	Month_Trans	Deal	Prev_Deal
30-Jul-16	Jul	12345	0
01-Aug-16	Aug	67890	12345
02-Aug-16	Aug	67891	67890
13-Aug-16	Aug	67892	67891
01-Sep-16	Sep	67889	67892

Target:

Transaction Date	Month_Trans	Deal	Prev_Deal	Reported
30-Jul-16	Jul	12345	0	Y
01-Aug-16	Aug	67890	12345	N
02-Aug-16	Aug	67891	67890	N
13-Aug-16	Aug	67892	67891	Y
01-Sep-16	Sep	67889	67892	Y

Reported is my derived column. Condition: Loop to compare first Col with remaining Columns on Deal and Prev Deal, if Deal=Prev deal then if Month of Deal and month of prev_deal the Reported as Y

Below is my script. It works fine for first loop. From second loop its not assigning any values.

BOX:

LOAD * INLINE [

Transaction, Deal, Prev_Deal, Month_Trans

30-Jul-16, 12345, 0, Jul

01-Aug-16, 67890, 12345, Aug

02-Aug-16, 67891, 67890, Aug

13-Aug-16, 67892, 67891, Aug

01-Sep-16, 67889, 67892, Sep

];

LET NumRows=NoOfRows('BOX');

FOR i=0 to $(NumRows)-1

trace OuterLoopStarts;

let _currDeal=peek('Deal',$(i));

let _currParDeal=peek('Prev_Deal',$(i));

let _currMonth=peek('Month_Trans',$(i));

trace $(_currDeal);

trace $(_currParDeal);

trace $(_currMonth);

FOR j=i+1 to $(NumRows)-1

trace InnerLoopStarts;

let nextDeal=peek('Deal',$(j));

let nextParDeal=peek('Prev_Deal',$(j));

let nextMonth=peek('Month_Trans',$(j));

trace $(nextDeal);

trace $(nextParDeal);

trace $(nextMonth);

if '$(_currDeal)'='$(nextParDeal)' then

trace 1stIfStarts;

if '$(_currMonth)'<>'$(nextMonth)' then

trace 2ndIfStarts;

Test:

load Transaction, Deal, Prev_Deal, Month_Trans,'Y' as Reported resident BOX where Deal='$(_currDeal)';

trace 2ndIfEnds;

end if

trace 1stIfEnds;

endif

trace InnerLoopEnds;

trace OuterLoopEnds;

drop table BOX;

Re: Loop not executing

Gysbert_Wassenaar — Sun, 24 Jul 2016 17:25:01 GMT

Temp:

LOAD

Date#([Transaction Date],'DD-MMM-YYYY') as [Transaction Date],

Month_Trans,

Deal,

Prev_Deal INLINE [

Transaction Date, Month_Trans, Deal, Prev_Deal

30-Jul-16, Jul, 12345, 0

01-Aug-16, Aug, 67890, 12345

02-Aug-16, Aug, 67891, 67890

13-Aug-16, Aug, 67892, 67891

01-Sep-16, Sep, 67889, 67892

];

Result:

LOAD

If((Month_Trans<>Previous(Month_Trans) and Previous(Prev_Deal)=Deal) or RecNo()=1,'Y','N') as Reported

RESIDENT

Temp

ORDER BY

[Transaction Date] desc

;

DROP TABLE Temp;

Re: Loop not executing

pavandbs — Mon, 25 Jul 2016 02:06:08 GMT

Hi Gysber,

Thanks for quick response. Unfortunately data wouldn't be in sorted order and no identifier to make sort. This is the one scenario I have inserted test data. so I can't use Previous function here.

Thanks,

Pavan

Re: Loop not executing

Gysbert_Wassenaar — Mon, 25 Jul 2016 07:48:44 GMT

I assumed the data would not be sorted. That's why I sorted it in the second load on the Transaction Date.