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]

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

[agsearle]

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

[#4]

Do you mean firstsortedvalue?

Regards,

Gordon

[agsearle]

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

[prieper]

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

[agsearle]

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?

[prieper]

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

[#9]

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;

[agsearle]

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.