Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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:
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.
Hi MayilVahanan,
Mapping the second table into first means? Using mapping load?
Please clarify.
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!
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: