Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
YarinInbar
Contributor II
Contributor II

Migrate 3 tables into one

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 
64
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
Labels (1)
3 Replies
udit_k
Partner - Creator II
Partner - Creator II

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. 

YarinInbar
Contributor II
Contributor II
Author

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

udit_k
Partner - Creator II
Partner - Creator II

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;