Solved: First Function() in a table graph - Qlik Community

dcd123456 · ‎2015-12-17

Hello

i have a date table like this

Id	usuario	txt
12	usr1	prueba1
534	usr2	prueba1
34	usr3	prueba1
3	usr4	prueba3
26	usr5	prueba2

i have created a table graph with the field Txt like dimension field, and a expresion to count the diferents txt fileds Count(distint Id), but

also i want to see in each row of the table the first value of the field id and usuario, i try the expresion first(id) but it don't return anything.

Somebody know how to show ony the first value of this field (id) for each each row.???

i have attached and excample

thanks in advance for your help

best regards

dcd

tamilarasu · ‎2015-12-17

Yes, we need some key field to identify the load order. That is the reason I suggested to modify the load script (RecNo or FirstValue syntax) else I would have suggested some expression (as he was not clearly mentioned his requirement. I thought he wants to show first value (he mentioned in subject line as first value) from the excel). RecNo() (Not Rowno()) works fine in the above cases. Anyways, he is happy with your solution.

View solution in original post

jyothish8807 · ‎2015-12-17

Hi dcd,

Can you please share the output you are expecting ?

Regards

KC

Best Regards,
KC

sunny_talwar · ‎2015-12-17

May be this:

Expression1:

SubField(Aggr(Concat(DISTINCT usuario, '|'), txt), '|', 1)

Expression2:

SubField(Aggr(Concat(DISTINCT Id, '|'), txt), '|', 1)

jyothish8807 · ‎2015-12-17

Nice logic sunny

Best Regards,
KC

sunny_talwar · ‎2015-12-17

Thank you

dcd123456 · ‎2015-12-17

Hello

this result with is valid for us, showing the first value of userid and id for each txt value, but we need do this in the expresion of the table graph not in the load script.

txt	count(DISTINCT Id)	usuario	Id
prueba1	5	usr1	12
prueba2	1	usr5	26
prueba3	2	usr4	3
prueba4	2	usr7	5

If it is not possible extract the first value in the expresion of the graph, also is valid for us show only one value of usuario and id, for example to the text "prueba1", is valid for us show one of next rows:

usuario	Id
usr1	12
usr2	534
usr3	34
usr8	323
usr10	444

only one value of usuario and id, linked with each value of txt.

thanks

regards

dcd

dcd123456 · ‎2015-12-17

Great solution!!!

Tanks very much

Best Regards

dcd

sunny_talwar · ‎2015-12-17

No problem. I am glad I was able to help.

Best,

Sunny

tamilarasu · ‎2015-12-17

Hi Sunny,

Can you check your expression against the below excel.

tamilarasu · ‎2015-12-17

I am not sure about his requirement but this will produce the same output .

Data:

LOAD RecNo () as Rec,

Id,

usuario,

txt

FROM

(ooxml, embedded labels, table is Hoja1);

Expressions: FirstSortedValue(usuario,Rec)

FirstSortedValue(Id,Rec)