topic Peek Previous lookup all returns '-' in App Development

Peek Previous lookup all returns '-'

43918084 — Mon, 20 Dec 2021 21:32:42 GMT

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

Re: Peek Previous lookup all returns '-'

marcus_sommer — Thu, 02 Apr 2020 07:41:20 GMT

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

Re: Peek Previous lookup all returns '-'

43918084 — Thu, 02 Apr 2020 08:01:07 GMT

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];

Re: Peek Previous lookup all returns '-'

marcus_sommer — Thu, 02 Apr 2020 08:10:23 GMT

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

Re: Peek Previous lookup all returns '-'

43918084 — Thu, 02 Apr 2020 08:20:26 GMT

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

Re: Peek Previous lookup all returns '-'

43918084 — Thu, 02 Apr 2020 08:25:56 GMT

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

Re: Peek Previous lookup all returns '-'

marcus_sommer — Thu, 02 Apr 2020 08:27:35 GMT

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

Re: Peek Previous lookup all returns '-'

43918084 — Thu, 02 Apr 2020 08:30:55 GMT

I apologize for the trouble.

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

Re: Peek Previous lookup all returns '-'

marcus_sommer — Thu, 02 Apr 2020 08:31:18 GMT

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

Re: Peek Previous lookup all returns '-'

marcus_sommer — Thu, 02 Apr 2020 08:34:51 GMT

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

Re: Peek Previous lookup all returns '-'

43918084 — Thu, 02 Apr 2020 08:45:05 GMT

Really sorry, I still get '-'. Below is the script I use. Sorry my fieldname is a bit long in the original version. I thought I should simplify it in my question . Hope you don't mind that I provide the actual fieldname for your review.

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

Rowno()-1 as row,
recno() as current,

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

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

Drop Table [LYCM];

Re: Peek Previous lookup all returns '-'

marcus_sommer — Thu, 02 Apr 2020 08:49:41 GMT

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];

Re: Peek Previous lookup all returns '-'

43918084 — Thu, 02 Apr 2020 08:51:39 GMT

Thank you very much. It works now. I really appreciate your patience.