Add calculation

[#1]

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

[#2]

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

[qlikmsg4u]

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;

[ankit777]

Hi

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;

[jagan · Accepted solution]

Hi,

Try Generic Load

Generic Load

The Generic Load

Regards,

Jagan.

[#6]

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

[ankit777]

hi

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;

[#8]

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

[jagan]

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.

[#10]

Hi Jagan,

Ok, big thanks Jagan youre a good problem solver.