Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

gargi_bardhan
New Contributor III

Straight table

Hi All,

Can any one help me in this:

   I/P                                 O/P in Report required

ID , Status                         ID , Status

111S,sold                            111S,  sold

111,wait                              111, X

333S,sold                            333S,  sold    

333,wait                              333 ,X

444S,sold                            444 , sold         

444,wait                              444, X

222,wait                             222,Y       

555,wait                              555,X                   

555S,sold                            555 , sold

999,wait                             999,Y

The requirement is:

If a ID has record as same ID suffix as 'S' & status = 'sold' then the status of the same ID without 'S' should change to 'X'

exm : ID = 111S has status = 'sold' so status of ID= 111 should change from 'wait' to 'X'

If there is a ID which doesnt have any record (same ID with suffix as 'S' & status = 'sold' )  then its status = 'Y'

ex ID= '222' or '999' it has no record as ID= 222S or ID = 999S ,so its staus should change from 'wait' to 'Y'

1) If the ID with suffix 'S' (ex. : 111S) then Status = 'sold' i.e No change

2) If the ID with suffix 'S'  (ex: 111S) & the same ID exist without 'S' (ex: 111 ,333,444,555)

  i) then Status for '111' = 'X' from 'wait' 

3) For ID = '222' or '999' there is no record in ID with suffix 'S' (222S or 999S not present ) , so the Status = 'Y'

Let me know if the above explanation is not clear.

Thanks In advance.

Regards,

Gargi

2 Replies
Sokkorn
Honored Contributor

Re: Straight table

Hi Gargi,

Try below script

[Data]:

LOAD * Inline [

    ID , Status

    111S,sold

    111,wait

    333S,sold

    333,wait

    444S,sold

    444,wait

    222,wait

    555,wait

    555S,sold

    999,wait];

[Flag]:

Mapping

LOAD

    SubField(ID,'S',1)            AS [FlagID],

    Count(SubField(ID,'S',1))    AS [FlagCount]

Resident [Data] Group by SubField(ID,'S',1);

[Data2]:

LOAD

    *,

    If([Flag]=1 and [tmpStatus]='wait','Y',

        If([Flag]<>1 and [tmpStatus]='wait','X',[tmpStatus]

    ))    AS [Status2];

LOAD

    ID        AS [ID2],

    Status    AS [tmpStatus],

    ApplyMap('Flag',SubField(ID,'S',1))    AS [Flag]

Resident [Data];

DROP Fields [tmpStatus],[Flag];

Regards,

Sokkorn

gargi_bardhan
New Contributor III

Re: Straight table

Hey Hi Sokkorn,

First of all thanks for your response.

It resolved in almost the same manner you mentioned.

Thanks again . .

Regards,

Gargi

Community Browser