topic Re: how to get a specific value on expression in QlikView

how to get a specific value on expression

Wed, 07 Nov 2012 16:07:24 GMT

I have a chart with balance by rowid for specific client

ie.

client transid balance rowid

00 111 0 1

00 212 5 2

00 301 8 3

00 432 5 4

00 523 0 5

01 645 5 6

01 767 10 7

01 843 15 8

01 934 20 9

01 102 15 10

01 115 18 11

02 128 20 12

02 130 5 13

I used a function aggr in expression to get last rowid for a specific client but i need to get balance for this rowid

aggr( max(ROWID), Client))

This function return for client 00 the rowid= 5, i need balance = 0

How i can get this?

Re: how to get a specific value on expression

goldnejea8 — Wed, 07 Nov 2012 18:07:58 GMT

Try using aggr( min(ROWID), Client)) to get the first balance.

Would you always need the first balance, or would it change depending on the scenario?

Re: how to get a specific value on expression

Gysbert_Wassenaar — Wed, 07 Nov 2012 18:30:53 GMT

You can use the firstsortedvalue function. Create a straight table, add client as dimension and two expressions:

Rowid: FirstSortedValue(rowid, -rowid)

Balance: FirstSortedValue(balance, -rowid)

See attached example

Re: how to get a specific value on expression

Wed, 07 Nov 2012 18:50:08 GMT

ok, balance is a acumulated field, the last rowid is the actual balance to the client. In the table i have dates, the data is sort by date.

Depending of the date selected i need get the last rowid to the client.

Re: how to get a specific value on expression

Wed, 07 Nov 2012 18:51:32 GMT

i used the firstsortedvalue fuction, but in somes cliente appears "-" null, i don´t know why?

Re: how to get a specific value on expression

Wed, 07 Nov 2012 19:14:42 GMT

ok.

the first two tables have problems with showing the data.

the next table have the correct values but only field saldo.

the last table is that you suggest made.

sorry for my english, i speak spanish.

Re: how to get a specific value on expression

Gysbert_Wassenaar — Wed, 07 Nov 2012 20:27:31 GMT

In the cases where firstsortedvalue returns a null, more than one value of balance_ref was returned. Check transid 91442. In a table box I get 4 records back for two cardcodes and two amounts each. It's not possible to determine which value is the one that should be used.

Re: how to get a specific value on expression

Wed, 07 Nov 2012 21:04:50 GMT

ok, thanks... I change the relations addin cardcode to table CxC_Balance_Ref. The difference now is too short.

i m newbie, how you can view the function return this values? please explain. Just i have 2 weeks with qlikview