Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
melissapluke
Partner - Creator
Partner - Creator

If statement in Load

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!

        

IDItemSub Item
1A2
2AT
3A
4B2
5BT
6B
7C2
8CT
9C
10AX
11AY
12AZ
13BX
14BY
15BZ
16CX
17CY
18CZ

        

ItemPrice APrice BPrice C
A1009080
B1119988
C2229282
1 Solution

Accepted Solutions
Not applicable

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.

img1.PNG

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

View solution in original post

2 Replies
Not applicable

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.

img1.PNG

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
Partner - Creator
Partner - Creator
Author

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