topic Re: Table1.FieldA=Table2.FieldB condition in Load Script in QlikView

Table1.FieldA=Table2.FieldB condition in Load Script

shyamcharan — Thu, 18 Feb 2016 02:42:32 GMT

Hi Experts,

I have a requirement to create a field values such as 'Within Area' and 'Outside Area'.

The definitions are as below:

IF(Table1.FieldA=Table2.FieldB,'Within Area','Outside Area')

However, I am unable to figure out how to do it.

Any help here is highly appreciated.

Regards,

Shyam.

Re: Table1.FieldA=Table2.FieldB condition in Load Script

sunny_talwar — Thu, 18 Feb 2016 02:48:06 GMT

May be by doing a join?

Table1:

LOAD * INLINE [

ID, C1, C2

1, ABC, 123

2, DEF, 456

3, GHI, 789

];

Join(Table1)

LOAD * INLINE [

ID, C21, C42

1, ABC, 123

4, ABC, 456

5, GRHI, 789

6, DEF, 456

7, GHI, 789

];

T1:

LOAD *,

IF(C21=C1,'Within','NotWithin') AS CA

Resident Table1;

DROP Table Table1;

Re: Table1.FieldA=Table2.FieldB condition in Load Script

shyamcharan — Thu, 18 Feb 2016 02:51:28 GMT

Thanks for the quick response Sunny.

It is my bad. In my scenario there will not be a direct join between the two tables. They are quite different in the Data Model. There will not be a joining column.

Sorry for the bad example I gave.

I appreciate your further help on this.

Thanks in advance.

Regards,

Shyam.

Re: Table1.FieldA=Table2.FieldB condition in Load Script

sunny_talwar — Thu, 18 Feb 2016 02:52:38 GMT

Alternatively you can try with Mapping Load to only check IDs in just Table1:

Table1:

LOAD * INLINE [

ID, C1, C2

1, ABC, 123

2, DEF, 456

3, GHI, 789

];

Table2:

LOAD * INLINE [

ID, C21, C42

1, ABC, 123

4, ABC, 456

5, GRHI, 789

6, DEF, 456

7, GHI, 789

];

MappingTable:

Mapping

LOAD ID,

C21

Resident Table2;

T1:

LOAD *,

IF(ApplyMap('MappingTable', ID) = C1,'Within','NotWithin') AS CA

Resident Table1;

DROP Table Table1;

Re: Table1.FieldA=Table2.FieldB condition in Load Script

shyamcharan — Thu, 18 Feb 2016 03:09:24 GMT

Hi Sunny,

Just got confused. Is the mapping load working on the common field ID in both the tables ?

I will not have a common join field or ID field in both the tables.

The tables would be joined to quite other tables in the data model.

Regards,

Shyam.

Re: Table1.FieldA=Table2.FieldB condition in Load Script

sunny_talwar — Thu, 18 Feb 2016 03:10:46 GMT

I guess can you share a sample which is more representative of your actual data?

Re: Table1.FieldA=Table2.FieldB condition in Load Script

shyamcharan — Thu, 18 Feb 2016 03:18:36 GMT

Hi Sunny,

Please accept my appologies for the confusion caused.

Please find the attached new sample.

However, in my actual data model the Table1 and Table 2 are quite apart with more tables in between somthing like the attached example.

Thanks in advance.

Regards,

Shyam.

Re: Table1.FieldA=Table2.FieldB condition in Load Script

sunny_talwar — Thu, 18 Feb 2016 03:27:48 GMT

What's the output that you are expecting?

Re: Table1.FieldA=Table2.FieldB condition in Load Script

shyamcharan — Thu, 18 Feb 2016 03:30:49 GMT

Hi Sunny,

I want to have a separate field in Table 1 that has just the values 'Within Area' and 'Outside Area' and that meets the below criteria.

IF(Table1.FieldA=Table2.FieldB,'Within Area','Outside Area')

-Shyam

Re: Table1.FieldA=Table2.FieldB condition in Load Script

sunny_talwar — Thu, 18 Feb 2016 03:39:21 GMT

Can you check the attached