topic Re: Multiplication of data in QlikView

Multiplication of data

rajni_batra — Tue, 11 Dec 2012 11:50:08 GMT

i have a table

PNR Ticket No Amount

1 1,2 100

2 3 100

but when i split ticket with subfield

PNR Ticket No Amount

1 1 100

1 2 100

2 3 100

now it gives me amount 200 for PNR 1 .. how to come out from this

Re: Multiplication of data

sujeetsingh — Tue, 11 Dec 2012 11:51:15 GMT

Please give a sample or explain your all scenario

Re: Multiplication of data

rajni_batra — Tue, 11 Dec 2012 12:00:48 GMT

i just wanted to split comma separed values into diff no of rows which is done but my amount is getting multiplied how to avoid that is my question

Re: Multiplication of data

rajni_batra — Tue, 11 Dec 2012 12:01:00 GMT

i just wanted to split comma separed values into diff no of rows which is done but my amount is getting multiplied how to avoid that is my question

Re: Multiplication of data

Or — Tue, 11 Dec 2012 12:09:02 GMT

What should be happening to your total? Should the 100 be associated only with the first row? Should it be split across all rows (in this example, 50 each)? Should each row have 100 associated with it, but when totalling, only unique PNRs are totaled?

Re: Multiplication of data

rajni_batra — Tue, 11 Dec 2012 12:14:53 GMT

sum(Amount) should be 100 only

PNR Ticket amount

1 1 100

1 2 100

Total 100

Re: Multiplication of data

rajni_batra — Tue, 11 Dec 2012 12:20:07 GMT

sum(Amount) should be 100 only

PNR Ticket amount

1 1 100

1 2 100

Total 100

Re: Multiplication of data

Tue, 11 Dec 2012 12:25:51 GMT

Try

Sum(Amount)/Sum(PNR)

Rebeca

Re: Multiplication of data

rajni_batra — Tue, 11 Dec 2012 12:33:54 GMT

Re: Multiplication of data

Or — Tue, 11 Dec 2012 15:02:39 GMT

Actually, that should be sum(Amount) / count(PNR). Depending on the specific data model, this may not work well (if PNR is a key). If that's the case, you may have to bring in a second instance of PNR just so you can use count() on it without problems.

Re: Multiplication of data

RedSky001 — Tue, 11 Dec 2012 15:26:35 GMT

Try this:

load * Inline [PNR, Ticket No, Amount

"1", "1,2","100"

"2", "3","100"];

NoConcatenate

load PNR

, subfield([Ticket No],',') as [Ticket No]

, Amount / count as Amount;

load * , substringcount([Ticket No],',') + 1 as count

Resident a;

drop Table a;

Re: Multiplication of data

kamalqlik — Wed, 12 Dec 2012 06:39:56 GMT

Hi Rajni ,

PFA Application.

Hope it helps you.

Regards

Kamal