Script Error in forming new table

[#1]

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.

[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

[#3]

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]

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

[#5]

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]

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 · Accepted solution]

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]

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