Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading data and joining issue

Hi all,

Have looked through the discussion for a solution but to no avail.

I have 2 tables

A - Identity Number, Visit Date, Amount_1

B - ID No., Admitted Date, Amount_2

Table A

Identity NumberVisit DateAmount
11/2/1992342
21/3/19927
21/3/199234

Table B

Identity NumberAdmitted DateAmount
21/3/2001645
21/3/200145
51/4/200290

I am trying to load 2 different tables with common fields Identity Number as they are named differently i managed to do this by renaming the field from table A to ID No. also.

However, I faced an issue on the data when creating charts in Qlik Sense.

I am trying to create a stacked bar chart based on

x axis - ID No. and Visit/Admitted Date (should be displayed as 1 field)

y axis - Total # of line items from table A and B

color - Type (line items from table A as type 1, line items from table B as type 2).

After creating the chart I realized that as the ID No. was combined together, the Visit Date and Admitted Date are reflected on the same line for same ID No.

This is an issue when I have the same ID No. with 2 different dates from table A and B.

Identity NumberVisit DateAdmitted DateAmount
11/2/1992342
21/3/19921/3/20017
21/3/19921/3/200134
21/3/19921/3/2001645
21/3/19921/3/20015
51/4/200290

As the chart takes the first date available from Visit/ Admitted Date to plot the chart and as a result the number is a combination of line items from both dates the values are reflected inaccurately.

Current:

Bar 1 - ID No.1, 1 instance on 1/2/1992 from Type 1

Bar 2 - ID No.2, 2 instances on 1/3/1992 from Type 1

Bar 3 - ID No.2, 2 instances on 1/3/1992 from Type 2

Bar 4 - ID No.5, 1 instance on 1/4/2002 from Type 2

Correct:

Bar 1 - ID No.1, 1 instance on 1/2/1992 from Type 1

Bar 2 - ID No.2, 2 instances on 1/3/1992 from Type 1

Bar 3 - ID No.2, 2 instances on 1/3/2001 from Type 2

Bar 4 - ID No.5, 1 instance on 1/4/2002 from Type 2

I am thinking that there is something wrong with the way the data was loaded but could not come to a conclusion on the right way to do it. Seek your kind responses. Thank you!

5 Replies
Not applicable
Author

See if you get better results by adding concatenate before the next similar data is loaded by the script (e.g  Concatenate LOAD instead of just load). if the data is close to matching.  Just a thought,


T

Not applicable
Author

Hi T,

The data fields are very different from each other the tables above are just a snippet of it.

I have tried concatenating before but the 2 tables does not seem to link when i do so.

I created a new field indicator in Qlik for both table A and table B so table A would have rows with Indicator - Type 1 and table B Indicator - Type 2 and the bar graph seems to work.

However when displaying pie charts the data does not seem to link well thinking that the way i loaded my data does not seem to be right. I have added my app for better understanding.

Thank you so much!

D

Not applicable
Author

I'll take a look... working on a project atm.  I might ask for your help next

petter
Partner - Champion III
Partner - Champion III

It is important to understand the relationship between the two tables so we can suggest the right way forward.

First of all by introducing a new common field you create a synthetic key in you data model - which is most often not very good.

Can you confirm whether:

The Park had a number of Exhibitions right? And the Identity Number is a person that is admitted to a park and goes to 0 or more exhibitions? The Admitted Date is not necessarily the same date as Visit Date is it? It could be or it could be some following dates?

If the only thing that should link the two tables is the Identity Number then you should name the Identicator differently like you do with the other fields except the Identity Number.

We can get back to how to graph this as long as you confirm how the fields and tables should relate...

Not applicable
Author

Hi,

Noted on the disadvantages of synthetic keys.

Park and Exhibitions visits are not related. However I am trying to link the 2 tables with Identity Number so that I am able to find out how many of the same people visited Exhibition and Park on the same date/ with the same status.

I have managed to come out with something using the qualify method. May I seek your kind advice if this is an appropriate way to proceed?

Thank you in advance!