Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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