3 Replies Latest reply: Dec 12, 2017 10:33 AM by Alex Timofeyev RSS

    Script Question

    Mark Ritter

      I have this issue with my incoming data.

       

      There is a Session ID for each row followed by a bunch of numeric values.

       

      In some cases I have 2 rows with the same Session ID.  Some of the columns have the same value and others are different.

      So in my Qlik table I am displaying 2 rows since the rows are not identical.

       

      For example the total processing time on each row is the same since this was the same session id.  However, the counts of what was processed are different.  I don't know why the source system does this. 

       

      I need to get these into a single row with some columns not summed and others do get summed into the final single row.

       

      I need to do this in the script. 

       

      Any ideas on the best way to handle this?

        • Re: Script Question
          Martin Pohl

          my ideas:

          load those values you need distinct in table one.

          load those datas you need multiple in a second table.

          Links both tables by those fields you Need (is sessionid enough?)..

           

          the other is:

          load your datas order by (sessionid)

          load

          sessionis,

          all fields,

          if(previous(sessionid=sessionid,1,0) as FlagMultiple

           

          sum or Count those values you need distinct with FlagMultiple = 1 only

           

          regards

          • Re: Script Question
            Simon Minifie

            Hi Mark,

             

            Is there any chance of some sample data?

             

            Thanks,

            Simon

            • Re: Script Question
              Alex Timofeyev

              you could do something like this:

               

               

              TempTable:

              LOAD

              SessionID as T_SessionID,

              TextValue as T_TextValue,

              NumericValue as T_NumericValue;

              SQL SELECT ... ;

               

              FinalTable:

              LOAD

              T_SessionID as SessionID,

              T_TextValue as TextValue,

              sum(T_NumericValue) as SumNumValues

              Resident TempTable

              Group by T_SessionID, T_TextValue;


              Drop table TempTable;



              Alternatively, if your data is in SQL database, you could do grouping right in the SQL query.