Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Leiro
New Contributor II

Read and update variable inside LOAD statement

Hi!

I have some ticketing data that looks like this:

ticket numberhistory_id action 
2019031310000010658617other
2019031310000010658618other
2019031310000010658631other
2019031310000010658632other
2019031310000010658633other
2019031310000010658634CLOSE
2019031310000010658635other
2019031310000010658636other
2019031310000010658639other
2019031310000010658640other
2019031310000010658641other
2019031310000010658642CLOSE
2019031310000010658643other
2019031310000010658644other
2019031310000010658645other
2019031310000010658646other
2019031310000010658710other
2019031310000010658880CLOSE
2019031310000010658881other
2019031310000010658892other
2019031310000010658895other
2019031310000010658902CLOSE

 

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 numberhistory_id action subticket number
2019031310000010658617other658634
2019031310000010658618other658634
2019031310000010658631other658634
2019031310000010658632other658634
2019031310000010658633other658634
2019031310000010658634CLOSE658634
2019031310000010658635other658642
2019031310000010658636other658642
2019031310000010658639other658642
2019031310000010658640other658642
2019031310000010658641other658642
2019031310000010658642CLOSE658642
2019031310000010658643other658880
2019031310000010658644other658880
2019031310000010658645other658880
2019031310000010658646other658880
2019031310000010658710other658880
2019031310000010658880CLOSE658880
2019031310000010658881other658902
2019031310000010658892other658902
2019031310000010658895other658902
2019031310000010658902CLOSE658902

 

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.

Labels (4)
4 Replies
ikarimov
New Contributor III

Re: Read and update variable inside LOAD statement

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;

Leiro
New Contributor II

Re: Read and update variable inside LOAD statement

Hi!

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

 

Highlighted
ikarimov
New Contributor III

Re: Read and update variable inside LOAD statement

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 )

Leiro
New Contributor II

Re: Read and update variable inside LOAD statement

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!