topic Re: Mapping Table problems - Temp Table the solution? in QlikView

Mapping Table problems - Temp Table the solution?

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

Hello,

I've been having trouble getting Mapping tables to work properly, and am wondering if the solution lies in using a Temp table.

In my particular situation, I have three tables with some common fields (F1, F2, and F3 in the example below) and some unique fields. I have concatenated them to form a single table (called "MainTable" in the example below).

MainTable:

Load F1, F2, F3, F4 From Table1;

Concatenate (MainTable)

Load F1, F2, F3, F5 From Table2;

Concatenate (MainTable)

Load F1, F2, F3, F6 From Table3;

The problem is that when I used ApplyMap within the script for one of the 3 tables, it did not apply for the remaining two (concatenated)

tables. For example, when I used the F1 ApplyMap within Table 1, it did not apply to the values of F1 within Tables 2 and 3, as I assumed it would.

My question is, where (and how) do I use the Mapping tables in such a way that they apply the same field from all three tables (that have been concatenated as in the above example)?

Is the solution to use a Temp Table? If so, can please you provide the exact script? I would like the original fields (e.g., F1) retained in MainTable as well as the Mapped fields (e.g., 'F1MapValue'). [I'm guessing the script to create the Temp table would be something like the following but am not certain...]

???

Noconcatenate Temp:

Load F1, ApplyMap('MapF1',F1,'Unknown') AS 'F1MappedValue'

Resident MainTable; Rename ??? ; Drop Table ???)

Thank you for any advice.

-John

Re: Mapping Table problems - Temp Table the solution?

vinieme12 — Tue, 01 Aug 2017 15:22:51 GMT

please provide a sample app or sample data set's; not sure what you are trying to do or achieve

How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Mapping Table problems - Temp Table the solution?

gsbeaton — Tue, 01 Aug 2017 15:28:24 GMT

Hi John,

You would need to use the ApplyMap() three times in your example, or maybe easier, do it all once the data is in memory as a resident load, eg:

Temp:
Load F1, F2, F3, F4 From Table1;
Concatenate (MainTable)
Load F1, F2, F3, F5 From Table2;
Concatenate (MainTable)
Load F1, F2, F3, F6 From Table3;

MainTable:
Load *
    ,ApplyMap('MapF1',F1,'Unknown') AS F1MappedValue
Resident Temp;
Drop Table Temp;

FYI, the following code would also work:

Temp:
Load F1, F2, F3, F4 From Table1;
Load F1, F2, F3, F5 From Table2;
Load F1, F2, F3, F6 From Table3;

MainTable:
Load *
    ,ApplyMap('MapF1',F1,'Unknown') AS F1MappedValue
Resident Temp;
Drop Table Temp;

Because Qlik automatically concatenates data from separate loads if the column names are identical.

Hope that helps

George

Re: Mapping Table problems - Temp Table the solution?

md_qlikview — Tue, 01 Aug 2017 15:30:17 GMT

Hi,

As per my understanding, u need to take resident of Maintable after concatenation done. And then try to use Applymap within newly loaded table like below

MainTable:

Load F1, F2, F3, F4 From Table1;

Concatenate (MainTable)

Load F1, F2, F3, F5 From Table2;

Concatenate (MainTable)

Load F1, F2, F3, F6 From Table3;

Final:

Load *, Applymap()

Resident MainTable;

Drop Table MainTable.

If you want script in detail, kindly share some sample data.

Thanks

Re: Mapping Table problems - Temp Table the solution?

jchambers123 — Tue, 01 Aug 2017 16:16:08 GMT

Vineeth, George, and Milind,

Thank you all for your helpful replies. The solution you suggested was correct. George-- thank you additionally for suggesting dropping the concatenation statement from the script-- anything to keep the script simple and clean is helpful.

Much appreciated.

-John