Straight table

[gargi_bardhan]

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

[Sokkorn]

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]

Hey Hi Sokkorn,

First of all thanks for your response.

It resolved in almost the same manner you mentioned.

Thanks again . .

Regards,

Gargi