Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hey Hi Sokkorn,
First of all thanks for your response.
It resolved in almost the same manner you mentioned.
Thanks again . .
Regards,
Gargi