Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
viveksingh
Not applicable

Scripting-Is it possible to replace values of one table with another table

Hi Experts,

I want to replace the values in table with the values in another table.

my first table is loading with SQL Query with select statement and 2nd table is loading with Excel.

Table1:

select filed1,

field2,

0 AS NAME,

100 AS NUMBER,

'-' AS GROUP

from TEST;

Table2:

LOAD

NAME,

NUMBER,

GROUP

from V2.xlsx

In Table1, NAME, NUMBER, GROUP are hard-coded values. but in Table2, NAME, NUMBER, GROUP are having different values.

now I want to replace these values in Table1 with the values in Table2.

Tags (3)
1 Solution

Accepted Solutions
rahulpawarb
Not applicable

Re: Scripting-Is it possible to replace values of one table with another table

Hello Vivek,

If Table1 has a Key field then relative key field should be there in Table2 to identify respective record. If you have matching Key values in Table1 and Table2 then you can join these two tables as below:

Table1:

SQL SELECT

Key,

filed1,

field2

from TEST;

JOIN (Table1)

Table2:

LOAD

Key,

NAME,

NUMBER,

GROUP

from V2.xlsx;

Else you can create three different Mapping tables for NAME, NUMBER & GROUP as below and consume it using ApplyMap function.

Mapping_Name:

Mapping LOAD

Key,

NAME

from V2.xlsx;

Mapping_Number:

Mapping LOAD

Key,

NUMBER

from V2.xlsx;

Mapping_Group:

Mapping LOAD

Key,

GROUP

from V2.xlsx;

Table1:

SQL SELECT

Key,

filed1,

field2,

ApplyMap('Mapping_Name', Key) AS NAME,

ApplyMap('Mapping_Number', Key) AS NUMBER,

ApplyMap('Mapping_Group', Key) AS NUMBER

from TEST;

Use above given draft solution to solve your problem.

Regards!

Rahul

4 Replies
vinieme12
Not applicable

Re: Scripting-Is it possible to replace values of one table with another table

rahulpawarb
Not applicable

Re: Scripting-Is it possible to replace values of one table with another table

Hello Vivek,

If Table1 has a Key field then relative key field should be there in Table2 to identify respective record. If you have matching Key values in Table1 and Table2 then you can join these two tables as below:

Table1:

SQL SELECT

Key,

filed1,

field2

from TEST;

JOIN (Table1)

Table2:

LOAD

Key,

NAME,

NUMBER,

GROUP

from V2.xlsx;

Else you can create three different Mapping tables for NAME, NUMBER & GROUP as below and consume it using ApplyMap function.

Mapping_Name:

Mapping LOAD

Key,

NAME

from V2.xlsx;

Mapping_Number:

Mapping LOAD

Key,

NUMBER

from V2.xlsx;

Mapping_Group:

Mapping LOAD

Key,

GROUP

from V2.xlsx;

Table1:

SQL SELECT

Key,

filed1,

field2,

ApplyMap('Mapping_Name', Key) AS NAME,

ApplyMap('Mapping_Number', Key) AS NUMBER,

ApplyMap('Mapping_Group', Key) AS NUMBER

from TEST;

Use above given draft solution to solve your problem.

Regards!

Rahul

viveksingh
Not applicable

Re: Scripting-Is it possible to replace values of one table with another table

Thanks for the reply Rahul.

rahulpawarb
Not applicable

Re: Scripting-Is it possible to replace values of one table with another table

Cheers,

Rahul