topic Re: Read and update variable inside LOAD statement in QlikView

Read and update variable inside LOAD statement

Leiro — Sat, 16 Nov 2024 04:06:11 GMT

Hi!

I have some ticketing data that looks like this:

ticket number	history_id	action
2019031310000010	658617	other
2019031310000010	658618	other
2019031310000010	658631	other
2019031310000010	658632	other
2019031310000010	658633	other
2019031310000010	658634	CLOSE
2019031310000010	658635	other
2019031310000010	658636	other
2019031310000010	658639	other
2019031310000010	658640	other
2019031310000010	658641	other
2019031310000010	658642	CLOSE
2019031310000010	658643	other
2019031310000010	658644	other
2019031310000010	658645	other
2019031310000010	658646	other
2019031310000010	658710	other
2019031310000010	658880	CLOSE
2019031310000010	658881	other
2019031310000010	658892	other
2019031310000010	658895	other
2019031310000010	658902	CLOSE

I have many logged events for the same ticket and some of them mean that the ticket was closed (flagged CLOSE).

For reporting purposes I must relate all logged events with the closest next closing one.

I tried using peek(), rowno() and other resources to create that index but couldn't find the way to get to this:

ticket number	history_id	action	subticket number
2019031310000010	658617	other	658634
2019031310000010	658618	other	658634
2019031310000010	658631	other	658634
2019031310000010	658632	other	658634
2019031310000010	658633	other	658634
2019031310000010	658634	CLOSE	658634
2019031310000010	658635	other	658642
2019031310000010	658636	other	658642
2019031310000010	658639	other	658642
2019031310000010	658640	other	658642
2019031310000010	658641	other	658642
2019031310000010	658642	CLOSE	658642
2019031310000010	658643	other	658880
2019031310000010	658644	other	658880
2019031310000010	658645	other	658880
2019031310000010	658646	other	658880
2019031310000010	658710	other	658880
2019031310000010	658880	CLOSE	658880
2019031310000010	658881	other	658902
2019031310000010	658892	other	658902
2019031310000010	658895	other	658902
2019031310000010	658902	CLOSE	658902

If only the was a way to read and update a variable inside a LOAD statement it wolud be so easy.

Can you please help me find the way?

Thanks!

EDIT: the subticket number doesn't have to be the history_id of the closing entry.

Re: Read and update variable inside LOAD statement

ikarimov — Wed, 20 Mar 2019 18:37:42 GMT

Try this:

data:
LOAD *,if(F3='CLOSE',F2) as Row INLINE [
F1, F2, F3
2019031310000010, 658617, other
2019031310000010, 658618, other
2019031310000010, 658631, other
2019031310000010, 658632, other
2019031310000010, 658633, other
2019031310000010, 658634, CLOSE
2019031310000010, 658635, other
2019031310000010, 658636, other
2019031310000010, 658639, other
2019031310000010, 658640, other
2019031310000010, 658641, other
2019031310000010, 658642, CLOSE
2019031310000010, 658643, other
2019031310000010, 658644, other
2019031310000010, 658645, other
2019031310000010, 658646, other
2019031310000010, 658710, other
2019031310000010, 658880, CLOSE
2019031310000010, 658881, other
2019031310000010, 658892, other
2019031310000010, 658895, other
2019031310000010, 658902, CLOSE
];

data2:
NoConcatenate
//load F1,F2,F3,if(F3='CLOSE',null(),MinRow) as MinRow;
load
F1,F2,F3,
nummin(Row, peek( 'CloseRow')) as CloseRow
Resident data
order by F2 desc;

drop table data;

Re: Read and update variable inside LOAD statement

Leiro — Thu, 21 Mar 2019 09:06:51 GMT

Hi!

Thank you very much for your effort but it's not working since there's gaps in the F2 column.

Re: Read and update variable inside LOAD statement

ikarimov — Thu, 21 Mar 2019 10:19:14 GMT

hello, what gaps? can you post error by photo?

its working for me, you can look at metod and repeat it on your data.

build qvw for you )

Re: Read and update variable inside LOAD statement

Leiro — Thu, 21 Mar 2019 11:49:12 GMT

Hi,

Theres gaps in history_id (from 658636 to 658639), and the missing id's belong to another ticket that may have CLOSE action. I'm trying to fix it changing the order of the load.

Thanks!