topic Migrate 3 tables into one in Connectivity & Data Prep

Migrate 3 tables into one

YarinInbar — Wed, 05 Jul 2023 09:45:30 GMT

Hi,
I have these 3 tables:

SHIRTS_MONTH:

LOAD

BRANCHNAME,

Sum(QPRICE * LineQTY) as ShirtsRevenue

RESIDENT TRANSACTIONS

WHERE FAMILYNAME = 50

AND CURDATE >= MonthStart(Today())

GROUP BY BRANCHNAME;

OTHERS_MONTH:

LOAD

BRANCHNAME,

Sum(QPRICE * LineQTY) as OthersRevenue

RESIDENT TRANSACTIONS

WHERE FAMILYNAME =

51 or

52 or

53 or

54 or

55 or

56 or

57 or

58 or

59 or

62 or

63 or

65 or

60 or

10 or

61 or

AND CURDATE >= MonthStart(Today())

GROUP BY BRANCHNAME;

SHOES_MONTH:

LOAD

BRANCHNAME,

Sum(QPRICE * LineQTY) as ShoesRevenue

RESIDENT TRANSACTIONS

WHERE FAMILYNAME = 2

AND CURDATE >= MonthStart(Today())

GROUP BY BRANCHNAME;

I want to create a table that looks like this (in load editor)

BRANCH NAME	SHIRTS REVENUE	OTHERS REVENUE	SHOES REVENUES
A	59439	5842	59335
B	5939	5732	57372

Re: Migrate 3 tables into one

udit_k — Wed, 05 Jul 2023 10:27:40 GMT

Concatenate 3 Tables with creating Flag field.

SHIRTS_MONTH:
LOAD
BRANCHNAME,
Sum(QPRICE * LineQTY) as Revenue,
'ShirtsRevenue' as Flag
RESIDENT TRANSACTIONS
WHERE FAMILYNAME = 50
AND CURDATE >= MonthStart(Today())
GROUP BY BRANCHNAME;

LOAD
BRANCHNAME,
Sum(QPRICE * LineQTY) as Revenue,
'OthersRevenue' as Flag
RESIDENT TRANSACTIONS
WHERE FAMILYNAME =
51 or
52 or
53 or
54 or
55 or
56 or
57 or
58 or
59 or
62 or
63 or
65 or
60 or
10 or
61 or
64
AND CURDATE >= MonthStart(Today())
GROUP BY BRANCHNAME;

concatenate(SHIRTS_MONTH)
LOAD
BRANCHNAME,
Sum(QPRICE * LineQTY) as Revenue,
'ShoesRevenue' as Flag

RESIDENT TRANSACTIONS
WHERE FAMILYNAME = 2
AND CURDATE >= MonthStart(Today())
GROUP BY BRANCHNAME;

In Front End Table use Set analysis with Flag to have separate columns.

Re: Migrate 3 tables into one

YarinInbar — Wed, 05 Jul 2023 10:42:39 GMT

The problem is that I want to export the migrated table from the load editor to a csv file in my data source, so I am not using the front end

Re: Migrate 3 tables into one

udit_k — Wed, 05 Jul 2023 10:59:30 GMT

Than you can take resident of concatenated table and using if condition with flag create separate columns.

NoConcatenate

Final:
Load
BRANCHNAME,
if(wildmatch(Flag,'ShirtsRevenue'), Revenue) as ShirtsRevenue,

if(wildmatch(Flag,'OthersRevenue'), Revenue) as OthersRevenue,
if(wildmatch(Flag,'ShoesRevenue'), Revenue) as ShoesRevenue

resident SHIRTS_MONTH;
drop table SHIRTS_MONTH;