Resultant from 2 Tables not fully linked.

[#1]

Hi All,

I have 2 Tables - Table 1 and Table 2

In Table 1 - Mapping Table,

There are 4 fields

Mapping1	Mapping2	Value1	Value2
A	AA	99.00%	344
B	BB	89.00%	45
C	CC	95.00%	55
D	AA	94.50%	65
E	CC	98.00%	43
F	DD	99.50%	436
G	EE	98.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

ID	Column1	Col2_Mapping1	CompanyName	Column3	Column4
1	4	Z	Company1	16	64
2	5	B	Company2	20	100
3	6	A	Company3	24	144
4	7	G	Company4	28	196
5	2	J	Company5	8	16
6	5	D	Company2	20	100
7	3	K	Company4	12	36
8	5	G	Company2	20	100

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:    

Mapping1	Mapping2	Value1	Value2	Sum(Column1) from Table 2	Count(Column4) from Table 2	Sum(Column3) from table 2
A	AA	99.00%	344
B	BB	89.00%	45
C	CC	95.00%	55
D	AA	94.50%	65
E	CC	98.00%	43
F	DD	99.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

[agni_gold]

Hope this help.

[sunny_talwar]

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

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);

[#4]

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.

[#5]

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

[#6]

Hi Agnivesh,

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

Regards,

Anjali Gupta

[#7]

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

[Kushal_Chawda · Accepted solution]

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;

[#9]

check this if it suits your requirement

[sunny_talwar]

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);