Loading multiple tables into one and sorting fields

[adnbbh]

Hi everyone, again I'm stuck on a relatively basic problem and I'd appreciate some help..

The situation is following; I have three tables with products; they consist of fields Serial Number, Product, Category.

Photo above is a symbolic representation; actual tables have loads of entries but the structure is the same - basically three tables represent different categories but it happens often that the same product appears in mutiple tables which has been bugging me as I can't seem to make it work; basically I want to have one table in the end with all the entries, no duplicates (duplicates appear in all three tables) but I also want a duplicate to take a Category that I want - if you look at the code and the photo - product with 10032 appears in Table 1 and 2 but has different categories: if I execute the code (with where not exists())  in the final table i have Product 10032 with the category 'A' but I actually want the final table to take the category from the second table. i.e. 'B' - basically, I don't want duplicates but i also want entries from Table 2 and Table 3 to 'overwrite' entries in Table 1 if that makes sense - my code gives me Product 10032 but with category A..

Every help is appreciated!

Products:

LOAD Distinct
SN,
Product,
Category
FROM [lib://AttachedFiles/Table1.xlsm]
(ooxml, embedded labels, table is Buchungsartikel);

LOAD Distinct
SN,
Product,
Category
FROM [lib://AttachedFiles/Table2.xlsm]
(ooxml, embedded labels, table is Buchungsartikel)

Where not Exists(SN,SN);

LOAD Distinct
SN,
Product,
Category
FROM [lib://AttachedFiles/Table3.xlsm]
(ooxml, embedded labels, table is Buchungsartikel)

Where not Exists(SN,SN);

[Ksrinivasan]

hi,

provide excel data and your expected result,

ksrini

[manoranjan_d]

Please try this below code and let me know whether your requirement meeting to your expectation

Table:
LOAD SN,
Product,
Category,
'T1' as Table

FROM
[C:\Users\NM\Desktop\Desktop data\Scenarios\3mar\New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD SN,
Product,
Category,
'T2' as Table
FROM
[C:\Users\NM\Desktop\Desktop data\Scenarios\3mar\New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet2);

LOAD SN,
Product,
Category,
'T3' as Table
FROM
[C:\Users\NM\Desktop\Desktop data\Scenarios\3mar\New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet3)

;

NoConcatenate
T4_Temp:
LOAD
*,
SN&Product as SN_Product
Resident Table
Order by SN,Product,Table desc;
DROP Table Table;

NoConcatenate
T5_temp:
LOAD
* ,
if(SN_Product=Previous(SN_Product),'Y','N') as flag
Resident T4_Temp;


DROP Table T4_Temp;
NoConcatenate
T6_temp:
LOAD
*
Resident T5_temp
where flag='N';

DROP Table T5_temp;

[ArnadoSandoval · Accepted solution]

Hi @adnbbh 

You were very close, if you reverse the order you are loading your tables, starting with Table 3, followed by Table 2 and finalizing with Table 1, you get the result you are expecting.

All the products in Table 3 will be part of the result, over-writing their existing record in Table 2 and Table 1; while Table 2 records over-write just those in Table 1, and from Table 1 only the products not present in Table 2 and Table 3 are part of the result.

Products:
LOAD
    SN,
    Product,
    Category
FROM [lib://Sample_Data/Multiple_Tables.xlsx]
(ooxml, embedded labels, table is Table_3);

LOAD
    SN,
    Product,
    Category
FROM [lib://Sample_Data/Multiple_Tables.xlsx]
(ooxml, embedded labels, table is Table_2)
Where Not Exists(SN);

LOAD
    SN,
    Product,
    Category
FROM [lib://Sample_Data/Multiple_Tables.xlsx]
(ooxml, embedded labels, table is Table_1)
Where Not Exists(SN);

Hope this helps,

[Ksrinivasan]

hi,

AAAA:
LOAD
SN,
Product,
Category
FROM [lib://REPORT EXTRACTION /SSSS1.xlsx]
(ooxml, embedded labels, table is Sheet29);

join
LOAD
SN,
Product,
Category
FROM [lib://REPORT EXTRACTION /SSSS1.xlsx]
(ooxml, embedded labels, table is Sheet30);

SSSS:
Load *,
autoNumber(RowNo(),(SN & Product)) as Rank1
Resident AAAA
order by SN, Category desc;
Drop Table AAAA;

Load
SN,
Product,
Category
Resident SSSS
Where Match(Rank1, 1);

Result:

ksrini

[Ksrinivasan]

hi,

input tab1

tab2:

result:

ksrinivasan

[manoranjan_d]

Hi Srini,

I hope Arnaldo gave solution and thread closed

[adnbbh]

Hi @ArnadoSandoval,

thank you very much! This exactly solved my problem.