Calculated measure issue in Script

[prabir_c]

Dear Members,

I am facing a issue while concat two tables. Example, Below scenario-

Suppose, I have a details table like below (Details_Data.xlsx)- 

Site_code	Date	Icode	Sale	Tax
1	01-01-2021	A001	1000
1	02-01-2021	A002	2000
1	03-01-2021	A003	3000
1	04-01-2021	A004	4000
1	05-01-2021	A005	5000
2	01-01-2021	A001	1000	200
2	02-01-2021	A002	2000	400
2	03-01-2021	A003	3000	300
3	01-01-2021	A001	1000	200

and in Another Table (Details_Data_Tax.xlsx)I have only Tax related to Site_code=1

Site_code	Date	Icode	Tax
1	01-01-2021	A001	100
1	02-01-2021	A002	200
1	03-01-2021	A003	300
1	04-01-2021	A004	200
1	05-01-2021	A005	100

Now, I have merged two table in script :

Detail_Data:

LOAD Site_code as %_Site_Code,
Date as %_DATE,
Icode as %_ICODE,
Sale as #_SALE,
Tax as #_TAX
FROM
Detail_Data.xlsx
(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD Site_code as %_Site_Code,
Date as %_DATE,
Icode as %_ICODE,
Tax as #_TAX
FROM
Detail_Data_TAX.xlsx
(ooxml, embedded labels, table is Sheet1);

Final:
Load *,
#_SALE - #_TAX as #_TAXABLE
Resident Detail_Data;
DROP Table Detail_Data;

in front End, when I am manually calculating Sale- tax it is working, but in #_Taxable measure data is 0 for Site code=1

Please help! What I am missing?

[MayilVahanan]

HI @prabir_c 

Concatenate will append the second table to first table. It doesn't join. 
If we see the table viewer, we can understand the result..

For your case, you can do mapping the second table into first and do the calculation for Taxable. 
Or in front end, you can achieve the result.

[prabir_c]

Hi MayilVahanan,

Mapping the second table into first means? Using mapping load?

Please clarify.

[prabir_c]

Hi MayilVahanan,

I have to merge both table's tax amount together. And I don't want to change manually in front end for each and every report expression as =Sale- Tax for taxable amt. Because I have already used Taxable amt which contains the calculated measure value for Taxable (other than site code 1)

So, I have to do the changes in script only.

What to do for showing the tax and sale of site code=1 in a single line? Please help!

[MayilVahanan · Accepted solution]

Hi @prabir_c 

Try like below

MapTax:
Mapping
LOAD Site_code&'-'& Date&'-'& Icode as %_Key,
Tax as #_TAX
FROM
Detail_Data_TAX.xlsx
(ooxml, embedded labels, table is Sheet1);

Detail_Data:

LOAD Site_code as %_Site_Code,
Date as %_DATE,
Icode as %_ICODE,
Sale as #_SALE,
ApplyMap('MapTax',Site_code&'-'& Date&'-'& Icode, Tax) as #_TAX
FROM
Detail_Data.xlsx
(ooxml, embedded labels, table is Sheet1);

Final:
Load *,
#_SALE - #_TAX as #_TAXABLE
Resident Detail_Data;
DROP Table Detail_Data;

O/p in table viewer: