Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
PFA
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.
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);
Hi Chinnu,
This is pretty straight forward .
You don't even need to join both table.
PFA Solution
Regards,
Pankaj
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.
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;