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: 
Anonymous
Not applicable

Joining Tables & Linking Tables = Headache

I have posted this query before, but I didn't explain myself very well last time.

I have a problem with duplications in a report I am producing. After a bit of investigative work I now know my issue is to do with linking / joining tables, but I just can't quite get what I need.

All I need to do is link the tables in such a way that will associate a billing cycle correctly to the person & date without getting the duplications.  Joining tables is blowing my mind, no matter how many times I try to get it.

I have attached a file to demonstrate what I need.  Please note there are more than 2 fields in each of my real tables, the attached is for demo.

Any help appreciated.

Thanks

Phil

1 Solution

Accepted Solutions
michael_maeuser
Partner Ambassador
Partner Ambassador

sorry, was a bit confused...

that should help

View solution in original post

11 Replies
kedar_dandekar
Creator
Creator

Hi Phil,

I think, the problem with duplicates is primarily arising because of the BillingCycleDates table.

You are using BillingCycleName from this table in your chart, the join for this table results in multiple cycle names for the same date. Do you need this table for analysis?

If you comment out this table and in its place use the CycleName column present in BillingCycle table.

You should get the desired records..

HTH,

KD

Anonymous
Not applicable
Author

I do need this table as I will be using the other field dates within it to determine which one of the billing cycles is the latest billing date and publish date.

I realise this is the table that is causing the grief, but I do need it.

Phil

kedar_dandekar
Creator
Creator

As mentioned earlier, the BillingCycleName field in the BillingCycleDates table results in multiple cycle names for the same date, as there are more than one BillingCycleName entries for any given date.

You can keep this table for determining which one of the billing cycles is latest, however while using fields in charts, you should use it independent of the other tables, i.e. for the chart that you have in the sample you should not use fields from this table, in place of BillingCycleName use the CycleName column present in BillingCycle table.

For charts where you plan to use the Fields in BillingCycleDates table, use only fields from this table, as they are not uniquely related to the other tables, and using fields from other tables will result in duplicate rows.

- KD

michael_maeuser
Partner Ambassador
Partner Ambassador

can you give me the sample xls?

Anonymous
Not applicable
Author

Michael ... OK.  Uploaded.

In answer to KD.  I do understand, however I will be using both in a chart, as the report needs to show if it is published billing based on the date and the division they are in.  The report will be generated to be sent to all divisions based on their own division's balance as at the latest billing date, so doing this doesn't work.

I did previously manage to do this in a chart however the calculations caused 'out of virtual memory'

Regards

Phil

kedar_dandekar
Creator
Creator

Thanks, it would be helpful, if you can also provide a snapshot of the expected outcome you would like to achieve, where you would be using data from both sets of tables..

- KD

michael_maeuser
Partner Ambassador
Partner Ambassador

see atttached

Anonymous
Not applicable
Author


I don't fully understand what you have done (a bit of a novice),  however despite it doesn't seem to have worked as there this there is no data for coming through from the RosterCycleDates table.  i.e.  if I add the publish date, they are all blank.

I feel like I am missing something really simple here.  Can this not be done by simply joining the tables in the correct way?

Phil

Anonymous
Not applicable
Author

My expected result will be to have the raw data as follows:


pic1.jpg