First Value?

[agsearle]

I am loading data, as below, but I can't get the FIRST VALUE to work.

FKKVKP:
LOAD
BP,
CA,
Account_Det_ID,
FIRSTVALUE(BP) as First_BP,
FROM (qvd)
GROUP BY BP, CA, Account_Det_ID;

The resulting table shows, for example:

BP CA Account_Det_ID
1234 5555 DU
1234 5555 DM
1234 6666 DM
5678 1111 DU
5678 2222 DU

For the purposes of the result, I need to show only the first row of data, regardless what is on the following rows, where the BP number is the same. Hope this makes sense.

Any ideas?

[biester]

I think you should use peek() or previous().

Rgds,
Joachim

[agsearle]

I'm fairly new to Qlikview, so how would I use these in the script?

[biester · Accepted solution]

Find enclosed a hopefully self explaining little demo. Perhaps that helps.

Rgds,
Joachim

[agsearle]

Thanks for that Joachim, exactly what I wanted!

[michael_anthony]

If I read your requirements correctly, you want to see:

BP CA Acct...

1234 5555 DU

5678 1111 DU

IF so, shouldn't you then apply the FirstValue function to the CA and Account_Det_ID fields and remove them from the Group By so it is only grouping on BP.

[#7]

Michael Anthony wrote:
If I read your requirements correctly, you want to see:
BP CA Acct...
1234 5555 DU
5678 1111 DU
IF so, shouldn't you then apply the FirstValue function to the CA and Account_Det_ID fields and remove them from the Group By so it is only grouping on BP. <div></div>

i have a similar issue, how would one accomplish what you had clarified

BP CA Accct

1234 555 DU

5678 111 DU

please see my concern at the below link too. thanks

http://community.qlik.com/forums/t/44695.aspx