topic First Value? in QlikView

First Value?

agsearle — Thu, 15 Oct 2009 17:49:17 GMT

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?

First Value?

biester — Thu, 15 Oct 2009 17:55:29 GMT

First of all you should remove the comma before the "FROM" and after the "as First_BP" if it's also in your script.

Rgds,
Joachim

First Value?

agsearle — Thu, 15 Oct 2009 18:00:22 GMT

Well spotted, but yes that's not in the original script.

First Value?

Thu, 15 Oct 2009 18:26:47 GMT

Do you mean firstsortedvalue?

Regards,

Gordon

First Value?

agsearle — Thu, 15 Oct 2009 18:31:52 GMT

I don't know, would that work better in this situation? I understand that this only returns numeric values?

First Value?

prieper — Thu, 15 Oct 2009 19:21:27 GMT

What do you mean with not working? The FirstValue should work in any case and deliver something. Is the result Null or something else? The GROUP BY already implements a certain sorting - you may overrule this by adding ORDER BY.

HTH
Peter

First Value?

agsearle — Thu, 15 Oct 2009 19:56:17 GMT

The data returned, is exactly as the same as it is in the source document. My understanding of FIRSTVALUE, means in this case, it would only show the first row of data of each BP?

First Value?

prieper — Fri, 16 Oct 2009 00:49:29 GMT

FirstValue() can be only one value, which then is repeated over all records, it does not change with the fields indicated as GROUP BY

HTH
Peter

First Value?

Fri, 16 Oct 2009 01:32:23 GMT

You can try this:



TMP_TAB:
LOAD * INLINE [
BP, CA, Account_Det_ID
1234, 5555, DU
1234, 5555, DM
1234, 6666, DM
5678, 1111, DU
5678, 2222, DU
];
TMP_TAB1:
LOAD
ROWNO() AS NO,
BP,
CA,
Account_Det_ID
RESIDENT TMP_TAB
ORDER BY BP, CA, Account_Det_ID;

DROP TABLE TMP_TAB;
TAB1:
LOAD
*,
BP as First_BP
RESIDENT TMP_TAB1
WHERE NO =1;
DROP TABLE TMP_TAB1;
DROP FIELD NO FROM TAB1;

First Value?

agsearle — Fri, 16 Oct 2009 15:37:38 GMT

Thanks Fernando, I can see what you are trying to do, but all this does is put the data in order and give row numbers. The final table then returns only the first row of ALL the data, rather than the first row of each occurance of the BP, which is what I'm after.

How can you get the row numbers to restart each time it finds a different BP, like:

No BP CA Account_Det_ID
1 1234 5555 DU
2 1234 5555 DM
3 1234 6666 DM
1 5678 1111 DU
2 5678 2222 DU

I can then select all the 1's in the No column to get the data I'm after.

First Value?

biester — Fri, 16 Oct 2009 16:02:43 GMT

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

Rgds,
Joachim

First Value?

agsearle — Fri, 16 Oct 2009 16:10:28 GMT

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

First Value?

biester — Fri, 16 Oct 2009 16:39:27 GMT

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

Rgds,
Joachim

First Value?

agsearle — Fri, 16 Oct 2009 17:43:16 GMT

Thanks for that Joachim, exactly what I wanted!

First Value?

michael_anthony — Mon, 09 Nov 2009 10:38:18 GMT

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.

First Value?

Thu, 21 Apr 2011 21:55:13 GMT


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