If exist

[#1]

Don't know how to do this....

From table A I load the rows ITEM, DESCRIPTION for one specific store

From table B I load the rows ITEMID, COST PRICE, SALES PRICE

In the load script I only want to get the cost price and sales price from the items that was loaded from table A

ITEM in table A is the same as ITEMID from table B

How to do this?

I've tried with "where exist" and left keep but I'm not sure that I use the correct syntax.

Please help

[#2]

in order to use where exist your fields have to have the same name. meaning that either you rename ITEM to ITEMID or the opposit way.

regards,

MT

[sasikanth]

hi

Use Applymap function

you will get the output

[Gysbert_Wassenaar · Accepted solution]

Like this:

TableA:
Load ITEM, DESCRIPTION  from ....

left keep (TableA)

TableB:
Load ITEMID as ITEM, COST PRICE, SALES PRICE from ...

[its_anandrjs]

Try to load your table like

table A

Load

ITEM, DESCRIPTION

From Location;

table B

Load

ITEMID, COST PRICE, SALES PRICE

From Lcoation

Where Exists(ITEM,ITEMID);

[PradeepReddy]

If you want data

1) 2 seperate tables, use the 'Left Keep'

2) in single table, use the 'Left join'

[sasikanth]

Load  * from

TAB_B;

TAB_A:

load * Inline [

ITEM,DESCIPTION

.

.

.];

Load

ApplyMap(ITEMID,Descrption) as ITEM,

Costprice,

Selling price

resident TAB_B;

[its_anandrjs]

Or you can use simple join if you want data based on the first table then use join with (Left,Right,Inner) based on the requirement.

tableA:

Load

ITEM, DESCRIPTION

From Location;

Join

tableB:

Load

ITEMID, COST PRICE, SALES PRICE

From Lcoation

Where Exists(ITEM,ITEMID);

[#9]

You can use exists like this:

A:

Load ITEM,

  DESCRIPTION

From ...;

B:

Load

  ITEMID,

  COST,

  PRICE,

  SALES,

  PRICE

From ...

Where exists(ITEM,ITEMID);

/Ida