How to get rit of multiple ID's

[Anonymous]

Dear specialists,

In my Qlikview script I collected product information form different souces and reduced it to only the information I need.

It looks like this (it is in 1 Qlikview table)

ID	DIAMTR	GRADE	KLEUR	MERKEN
01FAA01004012
01FAA01004012				254
01FAA01004012			Geel/zwart
01FAA01004012		UNS S31254
01FAA01004012	1/2"
01FAA01004014
01FAA01004014				222
01FAA01004014			Geel/rood/zwart
01FAA01004014		-
01FAA01004014	1/3"

As you can see I have multiple ID's. What I want is to make a resident load from this table and make it look like:

ID	DIAMTR	GRADE	KLEUR	MERKEN
01FAA01004012	1/2"	UNS S31254	Geel/zwart	254
01FAA01004014	1/3	-	Geel/rood	222

Any suggestions?

Thanks Dennis.

[Gysbert_Wassenaar]

Try firstvalue:

Load

     ID,

     firstvalue(DIAMTR),

     firstvalue(GRADE),

     firstvalue(KLEUR)

from ...somewhere

group by ID;

[Anonymous]

Thanks for the suggestion Gysbert, but that does not work as I would expact.

It does get rit of the multple ID's but only gives 1 value per ID.

Like:

ID	DIAMTR	GRADE	KLEUR	MERKEN
01FAA01004012	1/2"
01FAA01004014	1/3

....

[#4]

Do a lookup:

lookup#( fieldname, matchfieldname, matchfieldvalue [, tablename] )

Returns the value of fieldname corresponding to the first occurrence of the value matchfieldvalue in the field matchfieldname.

Fieldname, matchfieldname and tablename must be given as strings (e.g. quoted literals).

The search order is load order unless the table is the result of complex operations such as joins, in which case the order is not well defined.

Both fieldname and matchfieldname must be fields in the same table, specified by tablename. If tablename is omitted the current table is assumed.

If no match is found, null is returned.

Example:

lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

So make a lookup at ur date, and throw back the other columns and make two dummy dates in a temptable

[Gysbert_Wassenaar · Accepted solution]

Darn. That may mean that you don't have nulls, but zero-length strings as values. Ok, try maxstring instead for the string fields and max for numeric fields.

[Anonymous]

Thanks Gysbert Maxstring() seems to work perfectly for me!