Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have two tables as below:
Table 1:
Trail-ID Created Date
1 20-Jul-2013
2 19-Jul-2013
3 18-Jul-2013
Table2
Trail-ID Start-Date End-Date ReasonCodes
1 17-Jul-2013 17-Jul-2013 RC1
1 18-Jul-2013 19-Jul-2013 RC2
I need to pick the last reason code where CreatedDate is between start and end dates and count the distinct trail-id's for that reason code .
Do we need to load the data accordingly in the script or can we handle this in expression? I tried both ways but no success.
Can anyone tell me how to do this in either script or expression?
Thanks
Ramakanth
Hi Ramakanth,
Quite hard script, but can help. Let try
DATA:
LOAD * Inline [
Trail-ID, Start-Date, End-Date, ReasonCodes
1, 17-Jul-2013, 17-Jul-2013, RC1
2, 18-Jul-2013, 19-Jul-2013, RC2];
SET vRecCount = NoOfRows('DATA')-1;
FOR i = 0 TO $(vRecCount)
vMinDate = NUM(Date#(PEEK('Start-Date',$(i),'DATA'),'dd-MMM-yyyy'));
vMaxDate = NUM(Date#(PEEK('End-Date',$(i),'DATA'),'dd-MMM-yyyy'));
vID = PEEK('Trail-ID',$(i),'DATA');
vReason = PEEK('ReasonCodes',$(i),'DATA');
IF i = 0 THEN
recCount = 0;
ELSE
recCount = NoOfRows('DATA2');
ENDIF
DATA2:
LOAD
$(vID) AS [_ID],
Floor(Date($(vMinDate) + RowNo()-$(recCount)-1)) AS [_Date],
'$(vReason)' AS [_Reason]
AUTOGENERATE 1
WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);
NEXT
DROP Table [DATA];
[MapReason]:
Mapping
LOAD
[_ID] &'|'& [_Date] AS [_Key],
[_Reason]
Resident [DATA2];
DROP Table [DATA2];
//************************************************************************
[FinalData]:
LOAD
[Trail-ID],
[Created Date],
ApplyMap('MapReason',[Trail-ID] &'|'& Floor(Date#([Created Date],'dd-MMM-yyyy')),Null()) AS [ReasonCode]
Inline [
Trail-ID, Created Date
1, 20-Jul-2013
2, 19-Jul-2013
3, 18-Jul-2013];
Regards,
Sokkorn
Hi Ramakanth,
Quite hard script, but can help. Let try
DATA:
LOAD * Inline [
Trail-ID, Start-Date, End-Date, ReasonCodes
1, 17-Jul-2013, 17-Jul-2013, RC1
2, 18-Jul-2013, 19-Jul-2013, RC2];
SET vRecCount = NoOfRows('DATA')-1;
FOR i = 0 TO $(vRecCount)
vMinDate = NUM(Date#(PEEK('Start-Date',$(i),'DATA'),'dd-MMM-yyyy'));
vMaxDate = NUM(Date#(PEEK('End-Date',$(i),'DATA'),'dd-MMM-yyyy'));
vID = PEEK('Trail-ID',$(i),'DATA');
vReason = PEEK('ReasonCodes',$(i),'DATA');
IF i = 0 THEN
recCount = 0;
ELSE
recCount = NoOfRows('DATA2');
ENDIF
DATA2:
LOAD
$(vID) AS [_ID],
Floor(Date($(vMinDate) + RowNo()-$(recCount)-1)) AS [_Date],
'$(vReason)' AS [_Reason]
AUTOGENERATE 1
WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);
NEXT
DROP Table [DATA];
[MapReason]:
Mapping
LOAD
[_ID] &'|'& [_Date] AS [_Key],
[_Reason]
Resident [DATA2];
DROP Table [DATA2];
//************************************************************************
[FinalData]:
LOAD
[Trail-ID],
[Created Date],
ApplyMap('MapReason',[Trail-ID] &'|'& Floor(Date#([Created Date],'dd-MMM-yyyy')),Null()) AS [ReasonCode]
Inline [
Trail-ID, Created Date
1, 20-Jul-2013
2, 19-Jul-2013
3, 18-Jul-2013];
Regards,
Sokkorn
Thanks. this works.