Issue in script(adding a condition using two diff fields from 2 tables)

[#1]

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

[luciancotea]

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

[Sokkorn · Accepted solution]

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

[#4]

Thanks. this works.