Loading Data with joins/concatenate, with 2 linked fields

Danelle — Wed, 25 Nov 2020 16:16:04 GMT

Good day

I really hope you can help me, I need to build a model that tracks all the different things that can happen to a sim card, but I'm struggling to find the best way to load the tables. Unfortunately all of the data has the ICCID as unique identifier as well as the Date (which I cannot rename, since I need the Master Calendar to work), this creates problems when trying to load the tables, so for instance everything that needs to be tracked per ICCID

Day of sale
Day of Rica
Day of Connection
Month of Activation
Amount of Recharge per Day
Amount of Usage per Day
Which months it was in a specific Regional Location

Since all of these tables have both ICCIDs and Dates in them, I usually concatenated the whole load and work out from there, the problem now comes in when trying to bring in Regional locations as well, since selecting a region effectively deselects all the info, I've tried Join, but it still created the duplicate entries on the ICCID, and Left join also doesn't work as the date messes that up as well. Is there a way to left join only using ICCID while populating all info on the date? Please help, I'm lost

Exampe of table create for a single ICCID:

ICCID	DATE_KEY	USAGE	REGION	CONNECTION_Count	RECHARGE	RICA_Count
991474942275	2019-11-01		Limpopo
991474942275	2019-11-01				10
991474942275	2019-11-05	20
991474942275	2019-11-05			1
991474942275	2019-11-05					1
991474942275	2019-11-05				10
991474942275	2019-11-06	5
991474942275	2019-11-07	10
991474942275	2019-11-08	2
991474942275	2019-11-08				10
991474942275	2019-12-01		Limpopo

Re: Loading Data with joins/concatenate, with 2 linked fields

Brett_Bleess — Tue, 14 Apr 2020 19:32:18 GMT

Check out the following Design Blog post, it may give you another avenue with the dates:

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Hopefully it is of some use.

Regards,
Brett

topic Loading Data with joins/concatenate, with 2 linked fields in QlikView

Loading Data with joins/concatenate, with 2 linked fields

Re: Loading Data with joins/concatenate, with 2 linked fields