Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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.

Tags (2)
1 Solution

Accepted Solutions
maxgro
Not applicable

Re: Script Error in forming new table

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

;

7 Replies
oscar_ortiz
Not applicable

Re: Script Error in forming new table

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

Not applicable

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.

oscar_ortiz
Not applicable

Re: Script Error in forming new table

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

Not applicable

Re: Re: Script Error in forming new table

Thank you for your efforts.  Unfortunately, it is taking the max of all of the records, which is the last cr_id, and that is the only cr_id that has values for Latest_TS and Latest_State.  Attached is hand edited excel file of a few records of what I am trying to get.

vivek_niti
Not applicable

Re: Script Error in forming new table

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

maxgro
Not applicable

Re: Script Error in forming new table

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

;

prieper
Not applicable

Re: Script Error in forming new table

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