Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
cancel
Showing results for 
Search instead for 
Did you mean: 
zahidrahim_ocp
Partner - Creator
Partner - Creator

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

5 Replies
shraddha_g
Partner - Master III
Partner - Master III

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
Master III
Master III

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
Partner - Master III
Partner - Master III

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
Master III
Master III

Yes,

however You lose table PRICES in Data Model.

shraddha_g
Partner - Master III
Partner - Master III

Yes..

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