1 Reply Latest reply: Sep 10, 2018 4:56 PM by Nicole Smith RSS

    If Count > 1 then various in Load Script

    Mark Perreault

      Hi All,

       

      I have the below table

       

      IDSubIDStateTypePriority
      11AMASmallHigh
      11BCTHigh
      22AVAMediumLow
      33AVTSmallHigh
      33BNHSmallMedium
      33CMESmallLow

       

       

      In my final table I want to roll up my SubIDs to one line item for 'ID' with the logic for state and type

      if(Count(State)>1,'Various',State) as State

      if(Count(Type)>1,'Various',Type) as Type

       

      leaving me with a final table of

       

      IDStateTypePriority
      1VariousSmallHigh
      2VAMediumLow
      3VariousSmallVarious

       

       

      Currently I am doing this by

       

      set NullInterpret = '';

       

       

      Table1:

      Load * Inline

      [

      ID, SubId, State, Type, Priority

      1,1A,MA,SMALL,HIGH

      1,1B,CT,,HIGH

      2,2A,VA,MEDIUM,LOW

      3,3A,CT,SMALL,HIGH

      3,3B,NH,SMALL,MEDIUM

      3,3C,ME,SMALL,LOW

      ]

      ;

       

       

      Left Join(Table1)

      Load Distinct

           ID,

           Count(State) as StateCount,

           Count(Type) as TypeCount,

           Count(Priority) as PriorityCount

      Resident Table1

      Group by ID;

       

      NoConcatenate

       

      Table2:

      Load Distinct

           ID,

           If(StateCount>1,'Various',State) as State,

           If(TypeCount>1,'Various',Type) as Type,

           If(PriorityCount>1,'Various',Priority) as Priority

      Resident Table1;

      Drop table Table1;

       

       

       

       

       

       

      Exit script

       

       

      My output table is this however

       

      IDStateTypePriority
      1VariousVariousVarious
      1Various-Various
      2LowMediumVarious
      3VariousVariousVarious

       

       

      Does anyone have any suggestions on how to accomplish what I am looking for?

       

      Thanks,

      Mark

        • Re: If Count > 1 then various in Load Script
          Nicole Smith

          Load script like this:

          SET NullInterpret='';
          
          
          Temp:
          Load * Inline [
           ID, SubId, State, Type, Priority
           1,1A,MA,SMALL,HIGH
           1,1B,CT,,HIGH
           2,2A,VA,MEDIUM,LOW
           3,3A,CT,SMALL,HIGH
           3,3B,NH,SMALL,MEDIUM
           3,3C,ME,SMALL,LOW
          ];
          
          
          Final:
          LOAD ID,
           IF(COUNT(DISTINCT State) > 1, 'Various', MINSTRING(State)) AS State,
           IF(COUNT(DISTINCT Type) > 1, 'Various', MINSTRING(Type)) AS Type,
           IF(COUNT(DISTINCT Priority) > 1, 'Various', MINSTRING(Priority)) AS Priority
          RESIDENT Temp
          GROUP BY ID;
          DROP TABLE Temp;
          

           

          Will return an output table like this:

          ID State Type Priority
          1VariousSMALLHIGH
          2VAMEDIUMLOW
          3VariousSMALLVarious