Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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
I assumed the data would not be sorted. That's why I sorted it in the second load on the Transaction Date.