Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Resultant from 2 Tables not fully linked.

Hi All,

I have 2 Tables - Table 1 and Table 2

In Table 1 - Mapping Table,

There are 4 fields

   

Mapping1Mapping2Value1Value2
AAA99.00%344
BBB89.00%45
CCC95.00%55
DAA94.50%65
ECC98.00%43
FDD99.50%436
GEE98.50%655

Two of them - Mapping1 and Mapping2 are used to Map different tables.

Also Combination of Mapping1 and Mapping2 forms a uniqueID.

In Table2 - Main Table,

There are 6 fields

   

IDColumn1Col2_Mapping1CompanyNameColumn3Column4
14ZCompany11664
25BCompany220100
36ACompany324144
47GCompany428196
52JCompany5816
65DCompany220100
73KCompany41236
85GCompany220100

ID is the primary key,

Column1 is the data pertaining to UniqueID

Col2_Mapping1 has the mapping same as Mapping1  in Table1

Column3 is derived from Column1

Column4 is derived from Column1 and Column3

Required Output:    

Mapping1Mapping2Value1Value2Sum(Column1) from Table 2Count(Column4) from Table 2Sum(Column3) from table 2
AAA99.00%344
BBB89.00%45
CCC95.00%55
DAA94.50%65
ECC98.00%43
FDD99.50%436

Mapping1 From Table1

Mapping2 From Table1

Value1 From Table1

Value2 From Table1

Sum(Column1) From Table2  ---- How to get this?

Count(Column4) From Table2  ---- How to get this?

Sum(Column3) From Table2  ---- How to get this?

if any Mapping1 (or Col2_Mapping1 in Table 2) doesn't have any value in Column1 then it should be 0.

Attached Sample data.

Hi stalwar1‌ , Please check if you can help.

Regards,

Anjali Gupta

1 Solution

Accepted Solutions

Re: Resultant from 2 Tables not fully linked.

Try like below

Column1_Map:

mapping LOAD Col2_Mapping1,

          Sum(Column1) as Column1

FROM Table2

Group by Col2_Mapping1;


Column3_Map:

mapping LOAD Col2_Mapping1,

          Sum(Column3) as Column3

FROM Table2

Group by Col2_Mapping1;


Column4_Map:

mapping LOAD Col2_Mapping1,

          Sum(Column4) as Column4

FROM Table2

Group by Col2_Mapping1;

Table1:

LOAD

Mapping1

Mapping2

Value1

Value2,

applymap('Column1_Map',Mapping1,null()) as Column1,

applymap('Column3_Map',Mapping1,null()) as Column3,

applymap('Column4_Map',Mapping1,null()) as Column4

FROM Table1;

11 Replies
agni_gold
Valued Contributor II

Re: Resultant from 2 Tables not fully linked.

Hope this help.

Re: Resultant from 2 Tables not fully linked.

Not sure I completely understand the final goal here, but is this what you want?

Capture.PNG

If it is, I used this as the script:

Table2:

LOAD ID,

    Column1,

    Col2_Mapping1,

    CompanyName

FROM

[SampleData (3).xlsx]

(ooxml, embedded labels, table is Table2);

MappingTable:

Mapping

LOAD Col2_Mapping1,

  Column1

Resident Table2;

DROP Table Table2;

Table1:

LOAD Mapping1,

    Mapping2,

    Value1,

    Value2,

    ApplyMap('MappingTable', Mapping1, 0) as Column1,

    ApplyMap('MappingTable', Mapping1, 0) * 4 as Column3,

    ApplyMap('MappingTable', Mapping1, 0) * ApplyMap('MappingTable', Mapping1, 0) * 4 as Column4

FROM

[SampleData (3).xlsx]

(ooxml, embedded labels, table is Table1);

Not applicable

Re: Resultant from 2 Tables not fully linked.

Hi Anjali,

I don't understand why we need to use such a complicated script.

A simple join will also serve your purpose,

Find attached QVW.

May be I am mistaken about the requirement. but the output you need in excel...you will get from this qvw file.

Not applicable

Re: Resultant from 2 Tables not fully linked.

Hi Khushboo,

I want the calculations to be done in back-end i.e. in the script and not on the dashboard or chart level.

Regards,

Anjali Gupta

Not applicable

Re: Resultant from 2 Tables not fully linked.

Hi Agnivesh,

I want the calculation to be done at the back end and not on the front end.

Regards,

Anjali Gupta

Not applicable

Re: Resultant from 2 Tables not fully linked.

Hi Sunny,

The calculation for

Sum(Column1) From Table2 

Count(Column4) From Table2 

Sum(Column3) From Table2 

needs to be done at the script level, based on the mapping column Mapping1 and not on the front-end (or the straight table chart).

Regards,

Anjali Gupta

Re: Resultant from 2 Tables not fully linked.

Try like below

Column1_Map:

mapping LOAD Col2_Mapping1,

          Sum(Column1) as Column1

FROM Table2

Group by Col2_Mapping1;


Column3_Map:

mapping LOAD Col2_Mapping1,

          Sum(Column3) as Column3

FROM Table2

Group by Col2_Mapping1;


Column4_Map:

mapping LOAD Col2_Mapping1,

          Sum(Column4) as Column4

FROM Table2

Group by Col2_Mapping1;

Table1:

LOAD

Mapping1

Mapping2

Value1

Value2,

applymap('Column1_Map',Mapping1,null()) as Column1,

applymap('Column3_Map',Mapping1,null()) as Column3,

applymap('Column4_Map',Mapping1,null()) as Column4

FROM Table1;

Not applicable

Re: Resultant from 2 Tables not fully linked.

check this if it suits your requirement

Re: Resultant from 2 Tables not fully linked.

You can try like this:

MappingTable:

Mapping

LOAD Col2_Mapping1,

  Column1

FROM

[SampleData (3).xlsx]

(ooxml, embedded labels, table is Table2);

Table1:

LOAD Mapping1,

     Mapping2,

     Value1,

     Value2,

     Sum(Column1) as Column1,

     Sum(Column3) as Column3,

     Sum(Column4) as Column4

Group By Mapping1, Mapping2, Value1, Value2;

LOAD Mapping1,

     Mapping2,

     Value1,

     Value2,

     ApplyMap('MappingTable', Mapping1, 0) as Column1,

     ApplyMap('MappingTable', Mapping1, 0) * 4 as Column3,

     ApplyMap('MappingTable', Mapping1, 0) * ApplyMap('MappingTable', Mapping1, 0) * 4 as Column4

FROM

[SampleData (3).xlsx]

(ooxml, embedded labels, table is Table1);