topic Re: Issue in script(adding a condition using two diff fields from 2 tables) in QlikView

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

Tue, 23 Jul 2013 19:53:38 GMT

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

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

luciancotea — Tue, 23 Jul 2013 19:57:30 GMT

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

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

Sokkorn — Wed, 24 Jul 2013 02:11:05 GMT

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

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

Fri, 09 Aug 2013 00:31:52 GMT

Thanks. this works.