topic Re: Add percentage calculation in QlikView

Add calculation

Tue, 08 Sep 2015 08:57:47 GMT

Hi,

i want to ask about add row for calculate percentage.

I have a data like this:

Date Qty Trx_ID

3/6/2015 5 1

3/6/2015 3 2

4/6/2015 10 1

4/6/2015 7 2

I want to add one trx_id let's say Trx_ID 3 that the Qty will calculate the Qty(TrxID=2)/Qty(TrxID=1)

So the result i expect is like this:

Date Qty Trx_ID

3/6/2015 5 1

3/6/2015 3 2

3/6/2015 3/5 3 ------------------------- add this row

4/6/2015 10 1

4/6/2015 7 2

4/6/2015 7/10 3 ----------------------------add this row

Really appreciate your help.

Thanks

Re: Add percentage calculation

Tue, 08 Sep 2015 09:38:35 GMT

Additional Info

If i had a table like this:

Brand Qty Type

A 10 Type A

A 5 Type B

B 15 Type A

B 6 Type B

I want to transform that table into this table

Brand Type A Type B

A 10 5

B 15 6

Thanks

Re: Add percentage calculation

qlikmsg4u — Tue, 08 Sep 2015 09:56:14 GMT

Try this

Test:

Load * Inline [

Date, Qty, Trx_ID

3/6/2015, 5, 1

3/6/2015, 3, 2

4/6/2015, 10, 1

4/6/2015, 7, 2];

Concatenate

Load Date,if(Date = Peek(Date), Qty/Previous(Qty)) as Qty,3 as Trx_ID Resident Test;

NoConcatenate

Load * Resident Test where IsNull(Qty)=0 Order by Date,Trx_ID;

DROP Table Test;

Re: Add percentage calculation

ankit777 — Tue, 08 Sep 2015 09:58:58 GMT

Try this

A:
LOAD * INLINE [
Brand, Qty , Type

A , 10 , Type A

A , 5 , Type B

B , 15 , Type A

B , 6 , Type B
];
NoConcatenate
B:
LOAD
Brand,
if(Type='Type A', Qty) as [Type A],
if(Type='Type B', Qty) as [Type B]
Resident A;
drop table A;

Re: Add percentage calculation

jagan — Tue, 08 Sep 2015 10:14:24 GMT

Hi,

Try Generic Load

Generic Load

The Generic Load

Regards,

Jagan.

Re: Add percentage calculation

Tue, 08 Sep 2015 10:19:08 GMT

Hi,

Thanks for your response,

but the result is like this :

Is it possible to get the result like this :

Brand TypeA TypeB

A 10 5

B 15 1

Thanks

Re: Add percentage calculation

ankit777 — Tue, 08 Sep 2015 10:26:04 GMT

you may try like this

A:
LOAD * INLINE [
Brand, Qty , Type

A , 10 , Type A

A , 5 , Type B

B , 15 , Type A

B , 6 , Type B
];
NoConcatenate
B:
LOAD
Brand,
Qty as [Type A]
Resident A where Type='Type A';
Left join
LOAD
Brand,
Qty as [Type B]
Resident A where Type='Type B';
drop table A;

Re: Add percentage calculation

Tue, 08 Sep 2015 11:20:36 GMT

Hi Jagan,

Thanks for your suggestion, it works perfectly.

i have another question, is it possible to load a field with numerical format ?

lets say

load

121 --> numerical field name

122

Resident ....

thanks

Re: Add percentage calculation

jagan — Tue, 08 Sep 2015 13:47:33 GMT

Yes, we can have column names as numeric. Check this sample script

LOAD

INLINE [

1,2

a, b];

If you got the answer please close this thread by giving Correct and Helpful Answers.

Regards,

Jagan.

Re: Add percentage calculation

Wed, 09 Sep 2015 04:24:32 GMT

Hi Jagan,

Ok, big thanks Jagan youre a good problem solver.

Re: Add percentage calculation

jagan — Wed, 09 Sep 2015 05:59:00 GMT

Glad to help you.

Regards,

Jagan.