Hello friends ,I am stucked in a question in which , i have to do the calculation during load time is it possible?.

[#1]

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.

[#2]

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

[stevedark]

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

[vikasmahajan]

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

[#5]

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 ;

[#6]

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;

[jagan]

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.

[#8]

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;

[#9]

Hello.

Thanks for reply,

Is it possible to apply multiple joins in multiple tables?

[stevedark]

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

http://www.quickintelligence.co.uk/