Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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;
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
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;
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
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.
Hi Jagan,
Ok, big thanks Jagan youre a good problem solver.