topic Re: Map multiple values into one table in QlikView

Map multiple values into one table

asalamun — Mon, 09 Oct 2017 16:10:42 GMT

I'm trying to map from multiple tables into one table, but when I do I get an SQL load statement error.

Map_Table1_Department:

Mapping SQL

Name,

Department

FROM 'Table1';

Map_Table2_Department:

Mapping SQL

Name,

Department

FROM 'Table2';

The two mapping tables load fine;

[5_EXT]:

LOAD

OrderID,

ApplyMap('Map_Table1_Department',OrderID,'') as Department,

ApplyMap('Map_Table2_Department',OrderID,'') as Department,

SQL SELECT *

FROM NewTable;

This is where I get the SQL Load error

The OrderID in the NewTable matches the Name from Table1 and Table2, but the new table doesn't have any departments, so I'm trying to map the Departments into it.

Any ideas are appreciated.

Thank you,

Re: Map multiple values into one table

marcus_sommer — Mon, 09 Oct 2017 17:00:46 GMT

I noticed the following salience:

Map_Table1_Department:

Mapping SQL Select

Name,

Department

FROM 'Table1';

Map_Table2_Department:

Mapping SQL Select

Name,

Department

FROM 'Table2';

[5_EXT]:

LOAD

OrderID,

ApplyMap('Map_Table1_Department',OrderID,'') as Department1,

ApplyMap('Map_Table2_Department',OrderID,'') as Department2;

SQL SELECT *

FROM NewTable;

- Marcus

Re: Map multiple values into one table

asalamun — Mon, 09 Oct 2017 17:30:57 GMT

I had tried that, but my issue is that I'm trying to use Department as the Dimension for a chart

and when I use Department1, Department2 it doesn't represent the data the way I want

Re: Map multiple values into one table

vishsaggi — Mon, 09 Oct 2017 18:42:46 GMT

can you share some sample data with your expected output?

Re: Map multiple values into one table

asalamun — Mon, 09 Oct 2017 19:17:25 GMT

This is the graph that I want, but I need it to be based on all records otherwise these numbers won't be accurate. Using Marcus's approach above this is the reflection of Table1, Department1. In total I need to also add Table2, Department2 into one whole dimension.

Does this clarify?

Re: Map multiple values into one table

vishsaggi — Mon, 09 Oct 2017 19:55:51 GMT

may be try this? Extending Marcus script try like:

Map_Table1_Department:

Mapping SQL Select

Name,

Department

FROM 'Table1';

Map_Table2_Department:

Mapping SQL Select

Name,

Department

FROM 'Table2';

Table1:

LOAD

OrderID,

ApplyMap('Map_Table1_Department',OrderID,'') as Department;

SQL SELECT *

FROM NewTable;

Concatenate(Table1)

Table1:

LOAD

OrderID,

ApplyMap('Map_Table2_Department',OrderID,'') as Department;

SQL SELECT *

FROM NewTable;

Re: Map multiple values into one table

marcus_sommer — Tue, 10 Oct 2017 05:28:27 GMT

You could also nest the mappings by putting the second mapping into the third parameter from the first mapping, like this one:

Map_Table1_Department:

Mapping SQL Select

Name,

Department

FROM 'Table1';

Map_Table2_Department:

Mapping SQL Select

Name,

Department

FROM 'Table2';

[5_EXT]:

LOAD

OrderID,

ApplyMap('Map_Table1_Department',OrderID,

ApplyMap('Map_Table2_Department',OrderID,'#NV')) as Department;

SQL SELECT *

FROM NewTable;

- Marcus