Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 DATE | TRADE NUM | Previous Acct Date |
20191231 | 503232H | |
20200131 | 503232H | 20191231 |
20191231 | 503237H | |
20200131 | 503237H | 20191231 |
20191231 | 960509H | |
20200131 | 960509H | 20191231 |
20200131 | 1096601H | |
20191231 | 1096602H |
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];
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
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];
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
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 DATE | TRADE NUM | Previous Acct Date |
20191231 | 503232H | TRADE NUM |
20200131 | 503232H | TRADE NUM |
20191231 | 503237H | TRADE NUM |
20200131 | 503237H | TRADE NUM |
20191231 | 960509H | TRADE NUM |
20200131 | 960509H | TRADE NUM |
If I use Previous("ACCTG DATE") , I get below result where the Previous acct Date is the current row's ACCTG DATE
ACCTG DATE | TRADE NUM | Previous Acct Date |
20191231 | 503232H | 20191231 |
20200131 | 503232H | 20200131 |
20191231 | 503237H | 20191231 |
20200131 | 503237H | 20200131 |
20191231 | 960509H | 20191231 |
20200131 | 960509H | 20200131 |
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
I apologize for the trouble.
If I use your latest suggestion, it returns '-'
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
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