Error in First Value expression

[ivanaksen]

Hello Guys,

Please support with bellow question:

I have a task in script to take data which is very previous before first value "ME21NU".
But the problem that when I am making expression FirstValue(FINAL_FLAG)-1 as Indicator, I dont have any data,  but NULL. Can you support, how it should looks like? 
Thanks in advance

TCODE&CHANGE_IND&AutoNumber(CHANGENR&TCODE&CHANGE_IND) as FINAL_FLAG

CDHDR_TMP2:
load *,
If(Left(FINAL_FLAG,6)='ME21NU',1,0) as Flag
resident CDHDR_TMP;
drop table CDHDR_TMP;

CDHDR_IND:
Load
CDHDR_KEY,
Flag,
FirstValue(FINAL_FLAG)-1 as Indicator
resident CDHDR_TMP2
where Flag=1
group by CDHDR_KEY,Flag;

Left join (CDHDR_TMP2)
Load*
resident CDHDR_IND;
Drop Table CDHDR_IND;

CDHDR:
Load*,
TCODE as TCODE1
resident CDHDR_TMP2
where Indicator=1;
Drop table CDHDR_TMP2;

[sunny_talwar]

Are you looking to get the second value? May be you need to use another way to do this? Would you be able to share some sample data to show the kind of data you have (dummy mocked up data will work to) and explain what you are looking to get from it?

[ivanaksen]

No,
In my originaly formula, it was necessary to take last value of Date(the red square on the print screen) , but later we understood that date should be, the last one, before second ME21N(on the print screen it is green square).
So I decided to make a key-FINAL_FLAG. Than another Flag, which indicates where is necessary ME21N (in source it could be 3-4 times of this ME21N).
So the easiest way was, to take FirstValue of ME21NU (U-is indicator) and minus 1. So we will take date, which we need.
All steps and keys in above part of script, which I attached are working, but when it begins expression FirstValue, there is 0 data.

 

[sunny_talwar]

I would do something like this

Table:
LOAD TCode,
 Max(Date) as MaxDate
Resident ....
Group By TCode;

Final:
LOAD FirstSortedValue(TCode, -MaxDate, 2) as Max2TCode
Resident Table;

DROP Table Table;

[pablolabbe]

Hi,

Please remember to post questions related to the product in the correct discussion board. 

Here is the list of discussion board for qlik products,  https://community.qlik.com/t5/Qlik-Products/ct-p/qlik-products

This post will be moved to the correct discussion board soon.

Thanks, Pablo

[ivanaksen]

unfortunately it doesnt work, i have no data after all 😞
I tried some variations with your variant, but still no result.
Do you have any Idea, why my FirstValue expression doesn`t work??

[sunny_talwar]

Because I don't think FirstValue can look at SecondValue just because you are doing -1 :)... Sorry, with the amount of information you are providing... it will not be easy to help you.

Best,
Sunny