Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Leiro
Contributor II
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
Contributor III
Contributor III

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
Contributor II
Contributor II
Author

Hi!

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

 

ikarimov
Contributor III
Contributor III

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
Contributor II
Contributor II
Author

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!