Facing issue with the deriving fields

[chinnu123]

Hi Experts,

I am facing a issue with the deriving the new fields.

I am having two tables

In one table I am having a fields

Name

Code

Key

In second table I am having fields

Name

Text

Here I need to write condition like if(code='ABC',Text) as Test

same way if(code='BCA',Text) as Test1

                if(code='BDS',Text) as Test2

Hope I am clear. Can anyone please help me on this

Here I am attaching sample data and qvw please have a look

Thanks in advance,

Thanks,

Chinnu.

[sunny_talwar]

Is this what you want?

Script

Table:

LOAD Key,

    Code,

    Name

FROM

[Sample data1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join (Table)

LOAD Text,

    Name

FROM

[sample data2.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

If(Match(Code, 'ABC', 'BCA', 'BDS'), Text) as Test

Resident Table;

DROP Table Table;

[nilesh_gangurde]

PFA

[Anonymous]

You can not use other table's field in the if condition(can only use fields in the same table). So in order to use you have to join two tables then only you can use it.

[sasiparupudi1]

Another Solution would be

MAP_NAME_TO_TEXT:
Mapping LOAD 
    Name,
    Text
FROM

(ooxml, embedded labels, table is Sheet1);

Final:
Load *,
If(Code='ABC',Text) as Test,
If (Code='BCA',Text) as Test1,
If(Code='BDS',Text) as Test2

;
LOAD Key,
    Code,
    Name,
    ApplyMap('MAP_NAME_TO_TEXT',Name,'NA') as Text
FROM

(ooxml, embedded labels, table is Sheet1);

[passionate]

Hi Chinnu,

This is pretty straight forward .

You don't even need to join both table.

PFA Solution

Regards,

Pankaj

[chinnu123]

Hi Sunny,

Thanks for your reply, I am getting error when I am putting only before the If condition can you please correct me if

I am wrong in some where. I ma attaching sample qvw file

Thanks,

Chinnu.

[sunny_talwar]

Try without Only function...

FinalTable:

LOAD *,

If(Code='ABC',Text) AS SAMPLE1,

If(Code='ABD',Text) AS SAMPLE2,

If(Code='BCA',Text) AS SAMPLE3

Resident Table;