topic Straight table in QlikView

Straight table

gargi_bardhan — Wed, 10 Jul 2013 07:41:28 GMT

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

Re: Straight table

Sokkorn — Wed, 10 Jul 2013 10:53:03 GMT

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

Re: Straight table

gargi_bardhan — Mon, 15 Jul 2013 07:26:59 GMT

Hey Hi Sokkorn,

First of all thanks for your response.

It resolved in almost the same manner you mentioned.

Thanks again . .

Regards,

Gargi