How to concatenate multiple tables and then calculate distinct count in Qlik Sense

[kasimyc]

Hi all,

I'm trying to get daily individual customer contact from 3 separate tables from 1 year old to the present.
each table can have different/same customers. It can be recorded in 3 different tables on the same day.
I get the result by combining these tables, but since the table has close to 10mio records, it is difficult for me to work on it.
Is it possible to just get the number of unique customers per day after merging the tables here?

sample code:

table dwh:

LOAD
customer_id as ID,
rep_timestamp as row_date

SQL select
customer_id,
rep_timestamp

FROM dwh.dwh.tchnl_log
where
rep_timestamp >='$(s_date)' and
rep_timestamp <='$(e_date)' and
rep_funccode ='CA007' and
rep_channelcode='58' and
rep_info like('%contactIB%);

Concatenate(dwh)

table b:
LOAD
cak_timestamp as row_date,
cak_customer as ID

SQL select
cak_timestamp,
cak_customer

FROM ods.cak_log
where
cak_timestamp >='$(s_date)' and
cak_timestamp <='$(e_date)' and
cak_succode ='Y' and
cak_fcode='K001';

Concatenate(dwh)

table c:
cal_timestamp as row_date,
cal_customer as ID

SQL select
cal_timestamp,
cal_customer

FROM ods.cak_log
where
cal_timestamp >='$(s_date)' and
cal_timestamp <='$(e_date)' and
cal_succode ='Y' and
substr("cal_user_id",10,1)='F';

data size will be useful when the output is like this: 

row_date Count(distinct ID)
12122020 141941
12132020 138925

Thank you for your help

[rwunderlich · Accepted solution]

CustomerCounts:
LOAD
  count(Distinct ID) as CustomerCount,
  row_date
  Resident dwh
  Group by row_date;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[kasimyc]

Hi Rob, 

I also have tables loaded prior to this merge and drop. The common value in these tables is the row_date column. my goal is to have it merge with row_date here and get the number of unique customers per day.

When I complete the query as above; Unfortunately, the "CustomerCount" column does not give the number of individual customers. When I get the customer numbers distinct with set expression, I get different result.
My second problem is that when I want to use the Date column in the interface, I see it as multiplexed. This is probably because the date field in the concatenate tables is in Timestamp format.
My third problem is that checking the data in the interface is very time consuming. Could this be because of the Timestamp date format?

[rwunderlich · Accepted solution]

You'll want to extract just the date portion (whole number) using floor()

Date(Floor(cal_timestamp)) as row_date,

See where you are after making that change. 

[kasimyc]

It was exactly the tip I was looking for. Sometimes the little things can make a big impact. 

Thanx Rob, all the best