How to replace the value of table.

[#1]

Hi community,

I have to replace value from one table  to another table .

Please refere below sample data -

Base data :

ID         BS        PS

1          A          B

2          C          D

3          A          B

4          A          A

5          E          H

Sub Table

ID      BS        PS

1       C           C

3       D           D

So my expected answer is -

ID         BS        PS

1          C          C

2          C          D

3          D          D

4          A          A

5          E          H

Thanks in Advance

[sunny_talwar · Accepted solution]

Try this:

SubTable:

LOAD * Inline [

ID,      BS,        PS

1,       C,          C

3,       D,           D

];

Concatenate(SubTable)

LOAD * Inline [

ID,         BS,        PS

1,          A,          B

2,          C,          D

3,          A,          B

4,          A,          A

5,          E,          H

]

Where not Exists(ID);

The above is just for demonstration, you would not be doing this using inline table. So for you, you might need something like this

SubTable:

LOAD ID,

           FieldNames

From SubTable;

Concatenate (SubTable)

LOAD ID,

           OtherFields

FROM Table

Where not Exists(ID);

[avinashelite]

Try like this

Temp:

LOAD ID

from

Sub Table;

Base_data:

LOAD

ID,      

BS,   

PS

from table

Base data

where not exist(ID);

LOAD

ID,     

BS,   

PS

from table

Sub_table;

hope this helps you

[Kushal_Chawda]

another way

Map_Bs:

mapping LOAD ID,

           BS

FROM SubTotal;

Map_Ps:

mapping LOAD ID,

          PS

FROM SubTotal;

BaseData:

LOAD ID,

           applymap('Map_Bs',ID,BS) as BS,

          applymap('Map_Ps',ID,PS) as PS

FROM BaseTable;

[avinashelite]

kush141087‌ the mapping table doesn't have all the id details you missed this i guess

[Kushal_Chawda]

Yes but i think for non matching ID it will give the BS and PS value from base table only, I guess if I am not wrong I don't have qlikview to check avinashelite

[avinashelite]

You rite kush141087‌ I didn't sawyour complete code ....mistake was mine

[#8]

Hi Friends,

Thanks for your quik reply.

Sunny i have used your option i got correct answer but can you please explaine me how where not exists syntax work here.

[sunny_talwar]

Where not exists check if ID is available in the field ID. If it is, then don't bring it in (because of NOT). If it doesn't exists than bring it in.

The thing to note here is that Where not Exists is checking against a field and not a field in a specific table. So it will basically check against all ID loaded before this table. So to make sure you don't run into issues, either rename the ID field to something unique or try to put this script in the early part of your script.

HTH

Best,

Sunny

[Anonymous]

Hi kushal,

your answer should working !!!

I got the ouput as needed by ishwar10..

//***********************************************************

map_bs:

mapping LOAD * INLINE

[

ID, BS

1, C

3, D

];

MAP_PS:

MAPPING LOAD * inline

[

ID, PS

1, C

3, D

];

basedata:

LOAD *,

  applymap('map_bs',ID,BS) AS BSMATCHED,

  APPLYMAP('MAP_PS',ID,PS) AS PSMATCHED

INLINE

[

  ID, BS, PS

  1, A, B

  2, C, D

  3, A, B

  4, A, A

  5, E, H

  ];

//*************************************************************

TABLE BOX