Previous

[arhadisqlik]

Hi everybody 

I have a problem with the Previous function. Probably just an understanding issue :-X

I have a resident load that looks like this 

Load

Carryforward_check_FLAG, 

ExcelSæson,

Previous(ExcelSæson) as PrevSæson,

Previous(Carryforward_check_FLAG) as PrevCarryFlag,

if((ExcelSæson<>Previous(ExcelSæson)) and (Carryforward_check_FLAG=Previous(Carryforward_check_FLAG)),1,0) as ExcelCarry,

resident Excel 

order by Carryforward_check_FLAG, ExcelSæson;

I am trying to create an if-statement, that marks rows with a 1 where the previous Carryforweard_check_FLAG is equal to current rows Carryforweard_check_FLAG  and the ExcelSæson changes.

Below is a data sample of the result. 

I would expect the 0 (fat, red text) to be 1 since ExcelSæson changes from 2017,3 to 2018,3 and Carryforward_check_FLAG does not change.

I can very well see that PrevSæson and PrevCarryFlag do not return the 'correct' previous value, so that explains the If-functions return result. By why does this happen?

Carryforward_check_FLAG	ExcelSæson	ExcelCarry	PrevSæson	PrevCarryFlag
AM-441631/WHGP59055	2017,3	0	2017,3	AM-441631/WHGP59055
AM-441631/WHGP59055	2017,3	0	2018,3	AM-441631/WHGP51070
AM-441631/WHGP59061	2017,3	0	2017,3	AM-441631/WHGP59055
AM-441631/WHGP59061	2017,3	0	2017,3	AM-441631/WHGP59061
AM-441631/WHGP59061	2018,3	0	2018,3	AM-441631/WHGP59061
AM-441631/WHGP59061	2018,3	1	2017,3	AM-441631/WHGP59061

Can anybody help with some an advice

Thanks in advancve

Best Regards Anders

[sunny_talwar]

If you look closely at PrevSeason, the row where you expect to see 0, have a PrevSeason  of 2018... so what this tells you is that while loading the data, the 2nd last row is probably the loaded after the last row... it's just displaying it in a different order... but if you want to see 1 for the second last row, then you probably need to add another field to Order by which would force 2nd last row to load before the last row.

Does that make sense?

[arhadisqlik]

Hi Sunny

I've been away during the weekend hence the long response time.

It makes sense, thanks.

However, I do not have a third field which can be used to force the order I want.

I've tried loading everything into a tmp-table that is ordered by Carryforward_Flag and season and then load from this but it seems to ignore the order from the tmp-table.

Basically I'm just trying to identify the Carryforward_flags that span multiple seasons. 

A count grouped by Carryforward_flag kills the script and wont work.

All ideas are welcome.

[sunny_talwar · Accepted solution]

You can add RowNo() field in the previous load and may be use that to sort... I just don't know what is the difference between seeing a 1 on the last row vs second last row? It is just the difference in the display... may be the front end display should switch the row?

[Brett_Bleess]

Anders, I am hesitant to post this, but I figured it might help you a little, it's a pretty short Design Blog post on previous v peek functions, but I thought it might help put something into perspective for you.  Sunny is pretty sharp, so you are in good hands there, just shout back if you need more help, and I am sure he will holler back or someone else may jump in as well.

https://community.qlik.com/t5/Qlik-Design-Blog/Peek-vs-Previous-When-to-Use-Each/ba-p/1475913

Regards,
Brett

[arhadisqlik]

Hi Brett Thank you for the link - it cleared a few things up for me, namely which part of the load each function operates on.

Sunny's suggestion with the rowno() did the trick - and now that I am back behind the keyboard it will get accepted as the solution 🙂

Best regards - and thanks again

Anders

[arhadisqlik]

Thank you Sunny.

The RowNo() solved my problem!

Enjoy the day

Best regards 

Anders