Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

zahidrahim_ocp
Contributor

Using column in other table in script

Dear Experts,

I have two tables which i want to join and use one column in the second table like below:

ORDERS:

OrdId

ItemId

Quantity

Amount

PRICES:

ItemId

Price

What i want

ORDERS:

OrdId

ItemId

Quantity*Price

Amount

PRICES:

Outer Join(Orders)

ItemId

Price

Regards,

Zahid Rahim

Tags (1)
5 Replies
shraddha_g
Honored Contributor III

Re: Using column in other table in script

Orders:

Load

           OrdId

            ItemId

            Quantity

            Amount

From OrderTable;

Left join (Orders)

PRICES:

Load

             ItemId

             Price

From PricesTable;

noconcatenate

Final:

Load

        OrdId

        ItemId

       Quantity*Price

        Amount

Resident Orders;

Drop table Orders;

antoniotiman
Honored Contributor III

Re: Using column in other table in script

Hi Zahid,

may be this

PRICES:
LOAD * Inline [
ItemId,Price
A,5
B,8]
;

ORDERS:
LOAD *,Lookup('Price','ItemId',ItemId,'PRICES')*Quantity as Value Inline
[
OrdId,ItemId,Quantity,Amount
1,A,10,20
1,B,20,20
2,A,15,10
2,B,25,10]
;

Regards,

Antonio

shraddha_g
Honored Contributor III

Re: Using column in other table in script

Also you can try,

Map_Price:

Mapping Load

             ItemId,

             Price

From PricesTable;

Orders:

Load

           OrdId,

            ItemId,

           Quantity,

            Quantity * Applymap('Map_Price',ItemId) as Newfield,

            Amount

From OrderTable;

antoniotiman
Honored Contributor III

Re: Using column in other table in script

Yes,

however You lose table PRICES in Data Model.

shraddha_g
Honored Contributor III

Re: Using column in other table in script

Yes..

If you are getting Price field in Order table then you don't need it. Unless same table is used for another purpose.

Community Browser