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;
next
trace OuterLoopEnds;
next
drop table BOX;
