If statement in Load

[melissapluke]

Hi All,

I have two tables, sample data attached.

I need to find a price for each of the IDs.

If sub item=2 then the price=price B.  If the sub item=T then price=price C.  If the sub item is anything other than those two options the price=price A.  Can anyone help me out? Thanks!

ID	Item	Sub Item
1	A	2
2	A	T
3	A
4	B	2
5	B	T
6	B
7	C	2
8	C	T
9	C
10	A	X
11	A	Y
12	A	Z
13	B	X
14	B	Y
15	B	Z
16	C	X
17	C	Y
18	C	Z

Item	Price A	Price B	Price C
A	100	90	80
B	111	99	88
C	222	92	82

[#2 · Accepted solution]

Hi Melissa,

One way to achieve your requirement is by using a mapping table.

I have included the script below in case you need it.

ITEM_PRICE_A:

MAPPING LOAD

    Item,

    "Price A"

FROM [lib://Sample/Sample Data.xlsx]

(ooxml, embedded labels, table is Pricing);

ITEM_PRICE_B:

MAPPING LOAD

    Item,

    "Price B"

FROM [lib://Sample/Sample Data.xlsx]

(ooxml, embedded labels, table is Pricing);

ITEM_PRICE_C:

MAPPING LOAD

    Item,

     "Price C"

FROM [lib://Sample/Sample Data.xlsx]

(ooxml, embedded labels, table is Pricing);

Fact_Table:

LOAD

    ID,

    Item,

    "Sub Item",

    if("Sub Item" = '2',Applymap('ITEM_PRICE_C',Item),

    if("Sub Item" = 'T',Applymap('ITEM_PRICE_B',Item),

         Applymap('ITEM_PRICE_A',Item)))as Price

FROM [lib://Sample/Sample Data.xlsx]

(ooxml, embedded labels, table is [Table A]);

Regards,

Christian

[melissapluke]

That worked! Thank you for taking the time to answer this.  I appreciate it!