Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have these 3 tables:
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 |
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.
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
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;