topic Re: How to replace the value of table. in QlikView

How to replace the value of table.

Thu, 21 Apr 2016 11:06:18 GMT

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

Re: How to replace the value of table.

sunny_talwar — Thu, 21 Apr 2016 11:08:51 GMT

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

Re: How to replace the value of table.

avinashelite — Thu, 21 Apr 2016 11:11:19 GMT

Try like this

Temp:

LOAD ID

from

Sub Table;

Base_data:

LOAD

ID,

BS,

from table

Base data

where not exist(ID);

LOAD

ID,

BS,

from table

Sub_table;

hope this helps you

Re: How to replace the value of table.

Kushal_Chawda — Thu, 21 Apr 2016 11:28:48 GMT

another way

Map_Bs:

mapping LOAD ID,

FROM SubTotal;

Map_Ps:

mapping LOAD ID,

FROM SubTotal;

BaseData:

LOAD ID,

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

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

FROM BaseTable;

Re: How to replace the value of table.

avinashelite — Thu, 21 Apr 2016 11:37:16 GMT

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

Re: How to replace the value of table.

Kushal_Chawda — Thu, 21 Apr 2016 11:46:59 GMT

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

Re: How to replace the value of table.

avinashelite — Thu, 21 Apr 2016 11:55:17 GMT

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

Re: How to replace the value of table.

Thu, 21 Apr 2016 12:04:05 GMT

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.

Re: How to replace the value of table.

sunny_talwar — Thu, 21 Apr 2016 12:10:49 GMT

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

Re: How to replace the value of table.

Anonymous — Thu, 21 Apr 2016 12:12:56 GMT

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

Re: How to replace the value of table.

Thu, 21 Apr 2016 12:18:41 GMT

Thank u Sunny,

Now i understand this.

Re: How to replace the value of table.

sunny_talwar — Thu, 21 Apr 2016 12:30:16 GMT

Kush how are you checking your responses before you post? Are you just doing out of your best guess? That is amazing

Re: How to replace the value of table.

Kushal_Chawda — Thu, 21 Apr 2016 12:40:51 GMT

Sunny, i just reply based on my experience not guess.. but yea without checking in Qv its like guess only.

Re: How to replace the value of table.

sunny_talwar — Thu, 21 Apr 2016 12:43:47 GMT

That's what I meant, sorry if it sounded otherwise. But that amazing. Are you working on another technology now?

Re: How to replace the value of table.

Kushal_Chawda — Thu, 21 Apr 2016 12:44:53 GMT

Avinash.. it's fine.. for moment u really made me think of the logic again.. bcuz it was from one of the Qv champ

Re: How to replace the value of table.

Kushal_Chawda — Thu, 21 Apr 2016 12:46:40 GMT

Dont be sorry bro.. u r rite.. no i am still working on Qv but probably will move to microstrategy

Re: How to replace the value of table.

sunny_talwar — Thu, 21 Apr 2016 12:50:16 GMT

Damn it!! Is Qlik going to loose one of the genius minds then . I hope you will continue to contribute here

Re: How to replace the value of table.

Kushal_Chawda — Thu, 21 Apr 2016 13:04:53 GMT

Yeah i will surely contribute.. also there are many things still to learn..

Re: How to replace the value of table.

Anonymous — Thu, 21 Apr 2016 13:06:05 GMT

Hi kushal,

your answer should working !!!

I got the ouput as needed by ishwar10..

thanks to kushal ...

sorry for posting although question has been Answer ....

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

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

Re: How to replace the value of table.

sunny_talwar — Thu, 21 Apr 2016 13:16:23 GMT

Way to go