topic Re: Facing issue with the deriving fields in QlikView

Facing issue with the deriving fields

chinnu123 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Facing issue with the deriving fields

sunny_talwar — Wed, 09 Aug 2017 12:15:30 GMT

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;

Re: Facing issue with the deriving fields

nilesh_gangurde — Wed, 09 Aug 2017 12:19:21 GMT

PFA

Re: Facing issue with the deriving fields

Anonymous — Wed, 09 Aug 2017 12:31:41 GMT

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.

Re: Facing issue with the deriving fields

sasiparupudi1 — Wed, 09 Aug 2017 13:25:04 GMT

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);

Re: Facing issue with the deriving fields

passionate — Wed, 09 Aug 2017 14:01:02 GMT

Hi Chinnu,

This is pretty straight forward .

You don't even need to join both table.

PFA Solution

Regards,

Pankaj

Re: Facing issue with the deriving fields

chinnu123 — Thu, 10 Aug 2017 01:24:50 GMT

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.

Re: Facing issue with the deriving fields

sunny_talwar — Thu, 10 Aug 2017 10:52:41 GMT

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;