2 Replies Latest reply: Jul 15, 2013 3:26 AM by gargi bardhan RSS

    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

        • Re: Straight table
          Sokkorn Cheav

          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