topic Re: Populating a multi-column table with another table in QlikView

Populating a multi-column table with another table

Thu, 23 Apr 2015 23:55:35 GMT

Morning guys

I've got a fairly simple problem, but i cannot get my head around how to resolve this !:

I have 2 tables, 'Main' and 'Task Date'

On my 'Main' table I have a multi column Task Number:

No.	Task A Number	Task B Number	Task C Number
1	10
2	15	16
3	20	21	22
4	25	28	29
5	30

On my 'Task Date' table I have 2 columns.

Task Number	Task Date
10	3- Mar
15	4 - Mar
16	7 - Mar
17	22 - April
18	25 - April

I want to populate the 'Main' table with information from the 'Task Date' table, the finished table should look like:

No.	Task A Date	Task B Date	Task C Date
1	3- Mar
2	13- Mar	31- Mar
3	5- Mar	22 - April	12 - April
4	14 - April	22 - April	22 - April
5	22 - April

What is the easiest way to this without thousands of loops?

Many thanks in advance

Re: Populating a multi-column table with another table

Ralf-Narfeldt — Fri, 24 Apr 2015 08:25:36 GMT

You can either first load the Task Date table, and then use the LookUp function in a preceding load to the Main load where you create the Task A Date... fields

Or you can load Task Date as a mapping table, and then use ApplyMap in the Main load

Re: Populating a multi-column table with another table

marcus_malinow — Fri, 24 Apr 2015 08:27:25 GMT

ApplyMap might be a suitable approach.

Do a MAPPING LOAD of the task date table, then when loading the Main table, so multiple ApplyMaps, using the previously loaded date table mapping.

Re: Populating a multi-column table with another table

Tue, 28 Apr 2015 03:09:18 GMT

Hey Marcus

I took your advice, and did the below:

TaskNum_map:

Mapping load

resident TaskNum_date;

temp_final:

NoConcatenate load

ApplyMap('TaskNum_map',[Task A Number]) as [Task A Number],

ApplyMap('TaskNum_map',[Task B Number]) as [Task B Number],

ApplyMap('TaskNum_map',[Task C Number]) as [Task C Number]

Which seems to work magically, but im curious as how ApplyMap works in the background, how does the function know which column to search and which column to replace?

Ie, In "ApplyMap('TaskNum_map',[Task A Number])"

how does it know to search through column "Task No" and replace with "Task Date"

What would happen if both column data type were the same - and if one row of data appears on both the columns?

Task Number	Task Date
18	200
17	300
200	400
15	500
10	600

Re: Populating a multi-column table with another table

marcus_malinow — Tue, 28 Apr 2015 07:32:52 GMT

Hi Charlie,

ApplyMap takes as its inputs a mapping table, and a lookup value. The mapping table should be defined as a two column table with a lookup column as it's first column, and a return value as it's second.

Then, when you use the ApplyMap, you're essentially looking up your lookup column, and returning the return value.

It really doesn't matter what data types you use - in fact QlikView is fairly type agnostic anyway. If you have multiple values of your lookup value though you should be aware that ApplyMap will return the first return value that it finds. In this way it differs from something like a Join.

Marcus

Re: Populating a multi-column table with another table

Tue, 28 Apr 2015 20:36:05 GMT

Ahh, thanks mate I see, so the first column in Map is always the look-up column, second the value to replace. Perfect mate, clear as day