Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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?
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.
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?
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
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
Thank you Sunny.
The RowNo() solved my problem!
Enjoy the day
Best regards
Anders