Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
ecabanas
Creator II
Creator II

Merging two tables

Hi,

I making a table to count the number of customers that bought a category.

I'm making this script to create the tables:

 

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////Customer_912///////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

min_date_912:
LOAD 
    MIN(Createat_Counter) AS MIN_DFINAN, 
    MAX(Createat_Counter) AS MAX_DFINAN
resident Customer_counter;
let vminyear=year(PEEK('MIN_DFINAN',0,'min_date_Sales'));
let vmaxyear=year(peek('MAX_DFINAN',0,'min_date_Sales'));

Counter_912:
LOAD 
	COUNT(DISTINCT SalesId_Counter) AS Contador_YearlyOrders_912,
	//Customer_Counter as Custaccount,
    $(vminyear) as CalendarYear_C
    //Customer_Counter &'-'&$(vminyear) as Contador_Key
    RESIDENT Customer_counter
    WHERE Year_Counter ='$(vminyear)'and Cat_level_3_Counter = '912'
    GROUP BY $(vminyear);//Customer_Counter;

YearDiff=('$(vmaxyear)')-('$(vminyear)');

FOR N=1 TO $(YearDiff)

let vminyear=$(vminyear)+1;//year(PEEK('MIN_DFINAN',0,'min_date_Sales'));

CONCATENATE(Counter_912)
LOAD *;
LOAD 
	COUNT(DISTINCT SalesId_Counter) AS Contador_YearlyOrders_912,
	//Customer_Counter as Custaccount,
    $(vminyear) as CalendarYear_C
    //Customer_Counter &'-'&$(vminyear) as Contador_Key
    RESIDENT Customer_counter
    WHERE Year_Counter ='$(vminyear)'and Cat_level_3_Counter = '912'
    GROUP BY $(vminyear);//ustomer_Counter;
    	
NEXT

drop table min_date_912;
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////Customer_911///////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

min_date_911:
LOAD 
    MIN(Createat_Counter) AS MIN_DFINAN, 
    MAX(Createat_Counter) AS MAX_DFINAN
resident Customer_counter;
let vminyear=year(PEEK('MIN_DFINAN',0,'min_date_Sales'));
let vmaxyear=year(peek('MAX_DFINAN',0,'min_date_Sales'));

Counter_911:
LOAD 
	COUNT(DISTINCT SalesId_Counter) AS Contador_YearlyOrders_911,
	//Customer_Counter as Custaccount,
    $(vminyear) as CalendarYear_C
    RESIDENT Customer_counter
    WHERE Year_Counter ='$(vminyear)'and Cat_level_3_Counter = '911'
    GROUP BY $(vminyear);//Customer_Counter;

YearDiff=('$(vmaxyear)')-('$(vminyear)');

FOR N=1 TO $(YearDiff)

let vminyear=$(vminyear)+1;//year(PEEK('MIN_DFINAN',0,'min_date_Sales'));

CONCATENATE(Counter_911)
LOAD *;
LOAD 
	COUNT(DISTINCT SalesId_Counter) AS Contador_YearlyOrders_911,
	//Customer_Counter as Custaccount,
    $(vminyear) as CalendarYear_C
    //Customer_Counter &'-'&$(vminyear) as Contador_Key
    RESIDENT Customer_counter
    WHERE Year_Counter ='$(vminyear)'and Cat_level_3_Counter = '911'
    GROUP BY $(vminyear);//Customer_Counter;
    	
NEXT

drop table min_date_911;

 

The result is two separated tables, 

2019-06-03_18h12_42.png

But I want to create only one table like

CalendarYear_CContador_YearlyOrders_912Contador_YearlyOrders_911
2012125

120

2013158

169

 

Any Idea how to merge them?

 

Thanks

Labels (1)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

By the look of it I assume you could do a join.

Contador_YearlyOrders:
NoConcatenate
Load
CalendarYear_C,
Contador_YearlyOrders_911
Resident Counter_911;
Drop table Counter_911;
Join (Contador_YearlyOrders)
Load
CalendarYear_C,
Contador_YearlyOrders_912
Resident Counter_912;
DROP TABLE Counter_912;

View solution in original post

2 Replies
Vegar
MVP
MVP

By the look of it I assume you could do a join.

Contador_YearlyOrders:
NoConcatenate
Load
CalendarYear_C,
Contador_YearlyOrders_911
Resident Counter_911;
Drop table Counter_911;
Join (Contador_YearlyOrders)
Load
CalendarYear_C,
Contador_YearlyOrders_912
Resident Counter_912;
DROP TABLE Counter_912;
ecabanas
Creator II
Creator II
Author

thank'sssssssssss