topic Re: Issue related to First Sorted Value in QlikView

Issue related to First Sorted Value

Anonymous — Wed, 23 Nov 2016 07:49:32 GMT

Hey friends,

Facing issue while calculating Statement Amount using FirstSortedvalue()

Transaction Id	Value Date	Bank code	Amount_1	Amount_2	Posting Date	Branch Code

BA0030160921-019	21-09-2016	BA0030	25000	25000	01-10-2016	B0060
BA0030160921-019	21-09-2016	BA0030	25000	0	01-10-2016	B0180

Master Table:-

Transaction Id	Value Date	Amount_1
BA0030160921-019	21-09-2016	250000

Transaction Table:-

Transaction Id	Branch Code	User ID
BA0030160921-019	B0060	SHANKARR
BA0030160921-019	B0180	33199

Expected Output

Transaction Id	Value Date	Bank code	Amount_1	Amount_2	Posting Date	Branch Code

BA0030160921-019	21-09-2016	BA0030	25000	25000	01-10-2016	B0060
BA0030160921-019	21-09-2016	BA0030	0	0	01-10-2016	B0180

Because Amount_1 is 25000 against the given Transaction Id.

Please help me out.

Girish Chhatani

Re: Issue related to First Sorted Value

prat1507 — Wed, 23 Nov 2016 08:00:05 GMT

Hi Girish

Can we have a complete view of your transaction table?

Regards

Pratyush

Re: Issue related to First Sorted Value

Anonymous — Wed, 23 Nov 2016 08:04:47 GMT

Thank you Pratyush for the prompt reply.

Actually its the complete view.

Moreover Link between Master and Transaction table is Transaction ID only.

Because there is 1 record in Master and 2 records against ID BA0030160921-019 with 2 different Branch code B0060 and B0180.

So 2 records are appearing in the chart.

I want while computing Amount_1 once 1 time 25000 should get reported.

Thank you,

Girish

Re: Issue related to First Sorted Value

jonathandienst — Wed, 23 Nov 2016 08:13:17 GMT

>>I want while computing Amount_1 once 1 time 25000 should get reported.

You need to supply a rule as to which row should be used, and which should be set to zero. From the info you have provided, the only difference between the rows appear to be the Branch Codes.

Re: Issue related to First Sorted Value

Anonymous — Wed, 23 Nov 2016 09:43:25 GMT

Thanks u very much for the reply!!!

above issue solved by ur aggr() on the basis of Transaction Id.

Warm Regards,

Girish Chhatani