Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hii All,
I am trying to apply all the condition of calculation during load time...
It is just like i want to create a field of sum(money_measure) for A and B where A and B are my other fields.
Please help me..
Thanks in advance.
Hi,
You can use Group by inside script.
Example:
LOAD
A,
B,
SUM(money_measure) as Tot_Money
RESIDENT MY_TABLE
GROUP BY A,B
Hi Nupur,
If you simply want to add two fields on a single row of your source data it is done like this:
LOAD
A,
B,
A+B as C
Sum is an aggregation function (ie. for adding up rows, not columns) so you would only need to use this if you were aggregating your load with a GROUP BY statement.
Steve
Hii
One possibility is this changes you can do into back end directly and load the field. In Qlikview also you can achieve the same using if statement IF ( sum(money_measure) , 'A','B' ) AS FIELD NAME at the time of load.
Hope this helps you.
Vikas
Hi
see this sample script
b:
load * inline [
invoiceno,amount,city
1,100,e
2,100,a
3,100,b
4,200,c
5,200,e
6,300,f
2,300,a
]
;
LOAD invoiceno,Sum(amount) as newamount Resident b Group by invoiceno,city ;
Hello Aryabhrahma,
thanks for reply.
If i am using your script like as follows, it is giving me error as i am trying to use multiple fields from two different tables:
b:
load * inline [
invoiceno,amount,city
1,100,e
2,100,a
3,100,b
4,200,c
5,200,e
6,300,f
2,300,a
]
;
b1:
LOAD * INLINE [
invoiceno, qty
1, 20
2, 10
3, 5
4, 2
];
LOAD invoiceno,if(invoiceno='6',amount*qty,amount)
as newamount
Resident b;
Hi,
There is no qty field in table b, if you want to do this you need to join both the tables.
b:
load * inline [
invoiceno,amount,city
1,100,e
2,100,a
3,100,b
4,200,c
5,200,e
6,300,f
2,300,a
]
;
LEFT JOIN(b)
LOAD * INLINE [
invoiceno, qty
1, 20
2, 10
3, 5
4, 2
];
LOAD invoiceno,if(invoiceno='6',amount*qty,amount)
as newamount
Resident b;
Regards,
Jagan.
Hi
check this script
b:
load * inline [
invoiceno,amount,city
1,100,e
2,100,a
3,100,b
4,200,c
5,200,e
6,300,f
2,300,a
]
;
join
LOAD * INLINE [
invoiceno, qty
1, 20
2, 10
3, 5
4, 2
];
LOAD invoiceno,if(invoiceno='6',amount*qty,amount)
as newamount Resident b;
Hello.
Thanks for reply,
Is it possible to apply multiple joins in multiple tables?
You can repeatedly join new tables onto ones that already exist in your data model. You can even join data that you have already loaded to other data you have already loaded with a RESIDENT statement. You need to keep your wits about you though as you can get into quite a mess if you are not careful with multiple joins.
Steve