In QlikView, I have a table Data and one database table A. Table A should be used twice (A_Left, A_Right). (Table A can have thousands of entries.)
My load script is:
A_Left:
Load a_id_left,
a_name_left
inline [
a_id_left, a_name_left
1, 'nwsnd'
2, 'dcsdcws'
3, 'fsdf' ];
A_Rigtht:
Load a_id_right,
a_name_right
inline [
a_id_right, a_name_right
1, 'nwsnd'
2, 'dcsdcws'
3, 'fsdf' ];
Data:
Load id,
a_id_left,
a_name_left as 'Name_Left',
a_id_right,
a_name_right as 'Name_Right',
data
inline [
id, a_id_left, a_right_id, data
1, 1, 2, 37
1, 1, 3, 18
1, 2, 3, 62
];
So my question is: What is the best way to use lookup tables in QlikView?
(Should I use MAPPING
and/or ApplyMap
? Why? Is that faster?)
One other part of the question is: Would it help change the data structure from star to table?
(I know that would cost more memory.) And, by the way: How could I put all data in one table
so that I can store it completely in one QVD file?
Thanks for help an ideas.
Best Answer
For simple lookups where you wish to look up a single value from another value you can use a
MAPPING
load and then use theApplyMap()
function. For example, say I have the following table:I have another table that contains UserID and UserName as follows:
I can then combine the above tables with
ApplyMap
as follows:ApplyMap is very fast and should not significantly slow down your load time (although it will not be as fast as a direct QVD load). However, as mentioned ApplyMap can only be used if you wish to map a single value into your table. For more fields, you will need to use a
join
(which is similar to a SQL JOIN) if you wish to combine your results into a single table.If you do not wish to join them into a single table (but keep it as a "star" scheme), just make sure that the fields that you wish to link are named the same. For example:
(I have removed your "name" fields from "Data" as it would fail to load).
This will then work in your QlikView document due to QlikView's automatic field associativity.
However, if you wish to have the data in a single table (e.g. for output to QVD) then in your case you will need to
JOIN
your two tables intoData
. We can rearrange some of the tables to make our life a bit easier, if we put yourData
table first, we can then join your other two tables on:This will then resort in a single table named "Data" which you can then output to QVD etc.
You may wish to think about optimising your "Table A" extract since it is almost being loaded twice, this may take some time (e.g. from long distance server etc.) so it may be better to grab your data in one go and then slice it once it's in memory (much faster). A quick example could be like the below: