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: 
43918084
Creator II
Creator II

Peek Previous lookup all returns '-'

I have below table.  The first 2 fields are loaded table fields. The 3rd field is what I want to achieve.

The logic is if the Trade Num is the same as the previous, return the previous ACCTG DATE, otherwise returns blank

As I m a new to the tool, I first try to test if I can get the previous ACCTG DATE using

Peek(TRADE NUM, RowNo()-1)

or Previous (TRADE NUM)

or Lookup('ACCTG DATE', 'TRADE NUM', TRADE NUM, 'TABLE')

All returned -. 

Hope I can get some expert guidance

 

ACCTG DATETRADE NUMPrevious Acct Date
20191231503232H 
20200131503232H20191231
20191231503237H 
20200131503237H20191231
20191231960509H 
20200131960509H20191231
202001311096601H 
201912311096602H 
1 Solution

Accepted Solutions
marcus_sommer

Try it with a change to:

Load
"GLOBAL TRADE NUM (P0221)",
"ACCTG DATE (P0045)",

Rowno() as CurrentRecord,
recno() as SourceRecord,

Peek('ACCTG DATE (P0045)') as check,

 RESIDENT [LYCM]
 order by "GLOBAL TRADE NUM (P0221)","ACCTG DATE (P0045)" ;
 
Drop Table [LYCM];

 

View solution in original post

12 Replies
marcus_sommer

Your fieldname contained a space respectively a special char and therefore the reference to the field needs to be wrapped with quotes/brackets like: Previous ([ACCTG DATE]).

Beside this you might need to sort your (resident) load appropriate with an order by statement and also some if-loops to react on a changing trade num or other exceptions.

- Marcus

43918084
Creator II
Creator II
Author

Thank you very much for your guidance

I have added () to my field but I still get '-'.  Hope I can get more guidance

Please find below my script:-

Load
"TRADE NUM",
"ACCTG DATE",

Peek(("TRADE NUM")) as check,

 

 RESIDENT [LYCM] Group by "TRADE NUM","ACCTG DATE" order by "TRADE NUM", "ACCTG DATE";
 
Drop Table [LYCM];

marcus_sommer

There are some differences between certain functions. Most functions expect "real" fieldnames but some like field/tables-functions expect string-references. Therefore change it to:

...
peek('Trade Num') as check
...

or use my above suggestion with previous() which expects a fieldname.

Beside this your load contained a group by statement but no aggregation within the load - usually this leads to an error ...

- Marcus

 

43918084
Creator II
Creator II
Author

Thank you very much for your immediate help again.

I follow your advice using peek('Trade Num'), but the result return the fieldname.  Also, if I remove the Group By, I get invalid expression error

ACCTG DATETRADE NUMPrevious Acct Date
20191231503232HTRADE NUM
20200131503232HTRADE NUM
20191231503237HTRADE NUM
20200131503237HTRADE NUM
20191231960509HTRADE NUM
20200131960509HTRADE NUM
43918084
Creator II
Creator II
Author

If I use Previous("ACCTG DATE") , I get below result where the Previous acct Date is the current row's ACCTG DATE

 

ACCTG DATETRADE NUMPrevious Acct Date
20191231503232H20191231
20200131503232H20200131
20191231503237H20191231
20200131503237H20200131
20191231960509H20191231
20200131960509H20200131
marcus_sommer

Sorry, I didn't consider the right spelling because Qlik is case sensitive by names, therefore try:

...
peek('ACCTG DATE') as check
...

Of course if there is an aggregation and you didn't showed the whole statement you need to keep it.

- Marcus

43918084
Creator II
Creator II
Author

I apologize for the trouble.

If I use your latest suggestion, it returns '-'

marcus_sommer

To ensure that your load is sorted like expected and previous/peek return the right values it's often helpful to add rowno() and recno() to the load - so it will be obvious what's happening (for this remove the group by + aggregation).

- Marcus

marcus_sommer

Just check that the fieldnames are really correct and not by mistake contain two spaces instead of one or similar. Further if you use something like peek('FIELD') you need also to include FIELD within the load itself.

- Marcus