topic Re: Re: Script Error in forming new table in QlikView

Script Error in forming new table

Fri, 05 Dec 2014 19:13:40 GMT

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 — Fri, 05 Dec 2014 19:53:06 GMT

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

Sat, 06 Dec 2014 00:22:55 GMT

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 — Sat, 06 Dec 2014 01:26:20 GMT

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: Re: Script Error in forming new table

Sat, 06 Dec 2014 02:20:14 GMT

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.

Re: Script Error in forming new table

vivek_niti — Sat, 06 Dec 2014 11:42:51 GMT

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

maxgro — Sat, 06 Dec 2014 14:00:02 GMT

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

;

Re: Script Error in forming new table

prieper — Sun, 07 Dec 2014 19:02:47 GMT

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