7 Replies Latest reply: Dec 7, 2014 2:02 PM by Peter Rieper RSS

    Script Error in forming new table

      Hi,

        I am relatively new to QlikView and even DB queries.  I am having a problem that I get a script error that I do not understand.  Basically, I am trying to create a new table from a resident one using a "where" clause.  Here is the simplified script.

       

      // Start Script

      State:

      Load id,

      state_id as CRS_ID,

      state_date as State_TS;    //This is a timestamp and has multiple entries per id

      SQL SELECT *

      FROM mydb.`cr_state`;

       

      Left Join (State)

      Load state_id as CRS_ID,

      name as State_Name;            //This is a string that has an entry for each state_id

      SQL SELECT *

      FROM mydb.state;

       

      Drop Field CRS_ID From CR_State;

       

      //Everything is fine to this point.  The table "State" looks good

      //Now I want to create a new table with only the latest state based on the "latest" timestamp on each record

       

      Latest:

      load id,

      State_Name as Latest_State

      State_TS as Latest_TS

      resident State where State_TS = max(Latest_TS);

       

      // End Script

       

      I get an error in the log "General Script Error".

      Any help would be greatly appreciated.  Thanks in advance.

        • Re: Script Error in forming new table
          Oscar Ortiz

          John,

          The problem is with your Max(Latest_TS).

           

          I'm assuming you wan to the Max value of your State Timestamp.

           

          You'll want to crete a variable that stores the max value and use that variable in your where clause.

           

          tmp:

          LOAD
          Max(State_TS) as Max_TS
          RESIDENT State;
          LET vMax_TS = peek('Max_TS');
          DROP Table tmp;

           

          Where State_TS = $(vMax_TS)

           

          Good luck

          Oscar

            • Re: Re: Script Error in forming new table

              Thank you, but it does not seem to work.  Here I have attached a highly reduced qvw that shows the data.  I would like the second table to be a single entry of each id with the latest State_TS and the associated State_Name.

                • Re: Script Error in forming new table
                  Oscar Ortiz

                  John,

                   

                  Try this:

                   

                  tmp:

                  Load

                    Max( State_TS ) As MaxTS

                  resident State;

                   

                   

                  Let vMaxTS = peek('MaxTS');

                   

                   

                  Latest:

                  load

                    cr_id,

                    State_Name as Latest_State,

                    State_TS as Latest_TS

                  resident State

                  where

                    Exists( MaxTS, State_TS )

                  ;

                   

                   

                  DROP Table tmp;

                   

                  There was an issue with the number being stored in the variable.  So I changed things up a bit.  Instead of comparing against the variable I used the where Exists.

                   

                  Give it a try and let me know if you have more issues.

                   

                  Good Luck

                  Oscar

                  • Re: Script Error in forming new table
                    Peter Rieper

                    You may reload the table with a new sorting-order: key and date descending. Then each record, where the key is different from the previous one, is the first one with a new key - sorting by date DESC it has to be always the latest one.

                     

                    TableSorted:

                    LOAD

                    Key,

                    Date,

                    IF(Key <> PEEK(Key, -1), 'I am the last')     AS Indicator

                    RESIDENT

                    MyTable

                    ORDER BY

                    Key,

                    Date DESC;

                    DROP TABLE MyTable;

                     

                    TableFiltered:

                    NOCONCATENATE LOAD * RESIDENT TableSorted WHERE Indicator = 'I am the last';

                    DROP TABLE TableSorted;

                     

                    HTH Peter

                • Re: Script Error in forming new table
                  Vivek Niti

                  Hi,

                   

                  Try this code.

                   

                  tab1:

                  LOAD *,

                    Timestamp#(Latest_TS,'DD-MM-YYYY hh:mm') AS Latest_TS1;

                  LOAD * INLINE [ 

                      cr_id, Latest_State, Latest_TS

                      40000, In-Progress, 14-11-2014 14:16

                      40001, Open, 28-10-2014 19:28

                      40002, In-Progress, 13-11-2014 19:39

                      40006, In-Progress, 03-11-2014 22:31

                      40007, Closed, 31-10-2014 9:15

                      40008, Open, 29-10-2014 2:05

                      40009, Open, 29-10-2014 3:07

                      40010, In-Progress, 14-11-2014 14:17

                      40011, Closed, 31-10-2014 9:14

                      40012, Analysis, 18-11-2014 11:07

                      40013, In-Progress, 14-11-2014 14:18

                      40014, In-Progress, 13-11-2014 14:33

                      40014, Analysis, 13-11-2014 15:33

                      40014, Close5, 13-11-2014 16:33

                      40015, Closed, 29-10-2014 16:22

                  ];

                   

                   

                  tab2:

                  LOAD

                    cr_id,

                    Timestamp(Max(Latest_TS1),'DD-MM-YYYY hh:mm') AS Max_Latest_TS

                  RESIDENT tab1

                  GROUP BY cr_id;

                   

                   

                  INNER JOIN(tab1)

                  LOAD cr_id,

                    Max_Latest_TS AS Latest_TS1

                  RESIDENT tab2;

                  DROP TABLE tab2;

                   

                  Note: Have added 2 records to create multiple states:

                       40014, Analysis, 13-11-2014 15:33

                       40014, Close5, 13-11-2014 16:33

                   

                  Regards,

                  Vivek

                  • Re: Script Error in forming new table
                    Massimo Grossi

                    you can use peek to understand when the id change and order by to decide which id you want to keep (max or min state) 

                     

                    New:

                    NoConcatenate Load

                    id,

                    State_Name as max_State_Name,

                    State_TS as max_State_TS

                    Resident State

                    where Peek(id) <> id

                    order by id, State_TS desc

                    ;