topic Re: First Function() in a table graph in QlikView

First Function() in a table graph

dcd123456 — Thu, 17 Dec 2015 09:36:19 GMT

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

Re: First Function() in a table graph

jyothish8807 — Thu, 17 Dec 2015 09:47:54 GMT

Hi dcd,

Can you please share the output you are expecting ?

Regards

Re: First Function() in a table graph

sunny_talwar — Thu, 17 Dec 2015 10:11:03 GMT

May be this:

Expression1:

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

Expression2:

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

Re: First Function() in a table graph

jyothish8807 — Thu, 17 Dec 2015 10:17:13 GMT

Nice logic sunny

Re: First Function() in a table graph

sunny_talwar — Thu, 17 Dec 2015 10:19:03 GMT

Thank you

Re: First Function() in a table graph

dcd123456 — Thu, 17 Dec 2015 10:22:18 GMT

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

Re: First Function() in a table graph

dcd123456 — Thu, 17 Dec 2015 10:34:49 GMT

Great solution!!!

Tanks very much

Best Regards

dcd

Re: First Function() in a table graph

sunny_talwar — Thu, 17 Dec 2015 10:36:14 GMT

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

Best,

Sunny

Re: First Function() in a table graph

tamilarasu — Thu, 17 Dec 2015 10:39:05 GMT

Hi Sunny,

Can you check your expression against the below excel.

Re: First Function() in a table graph

tamilarasu — Thu, 17 Dec 2015 10:47:46 GMT

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)

Re: First Function() in a table graph

sunny_talwar — Thu, 17 Dec 2015 10:58:17 GMT

If we are adding a RecNo() or RowNo(), we can definitely use what you proposed or change my expression to this:

SubField(Concat(DISTINCT usuario, '|', Key), '|', 1)

Aggr() function sorts the data by load order and that is why I proposed its use. But Not sure why it isn't working with Concat. I will have to do further investigation into that or may be swuehl‌ can help as he was the one who explained that Aggr() uses load order.

Best,

Sunny

Re: First Function() in a table graph

tamilarasu — Thu, 17 Dec 2015 11:05:38 GMT

I don't think the problem is caused by Aggr function. Even the above expression, produces the same result.

Re: First Function() in a table graph

sunny_talwar — Thu, 17 Dec 2015 11:17:08 GMT

Aggr is not a problem here, I was thinking more of a solution. Check this thread I just created -> Load order in Concat() function

Re: First Function() in a table graph

sunny_talwar — Thu, 17 Dec 2015 11:34:02 GMT

Here you go:

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

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

But I guess this solution is good for someone trying to do this on the front end. If we can alter the script slightly, I would definitely do RecNo() or RowNo() in the script.

Best,

Sunny

Re: First Function() in a table graph

swuehl — Thu, 17 Dec 2015 12:54:14 GMT

If for example the same usuario value appears for more than one txt, the suggested solution might still fail.

(for example, add these two lines to the excel file:

99	usr1	prueba5
11	usr2	prueba5

)

There is a difference between field load order and record load order.

If there are only distinct values in field usuario and / or Id, the solution with aggr() should work, if you are chosing the right fields in the aggr() dimension.

In other words, to make it work, I think you need to identify some kind of primary key field in your table (maybe Id?)

Re: First Function() in a table graph

sunny_talwar — Thu, 17 Dec 2015 13:11:32 GMT

So we still might need a unique id to for Aggr() function based on the dataset? That make sense. Thanks for chipping in and helping Stefan. Like always, your advice is always much appreciated.

Re: First Function() in a table graph

tamilarasu — Fri, 18 Dec 2015 02:34:22 GMT

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.

Re: First Function() in a table graph

sunny_talwar — Fri, 18 Dec 2015 10:29:13 GMT

Since we don't have a preceding load in the example, I don't see how is RecNo is different from RowNo() here?

And Finally you won the battle my Friend. Just to clarify, I never doubted what you proposed, I was just trying to provide a front end solution.

Re: First Function() in a table graph

tamilarasu — Mon, 21 Dec 2015 05:04:29 GMT

I have no idea about the front end solution in this particular case. So,I was interested to know, if there is any. Just now noticed, by mistake I have entered a wrong comment (It should be OR )

Cheers