Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

philmywallet
Contributor

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
Contributor III

Re: Re: Re: Joining Tables & Linking Tables = Headache

sorry, was a bit confused...

that should help

11 Replies
kedar_dandekar
Contributor

Re: Joining Tables & Linking Tables = Headache

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

philmywallet
Contributor

Re: Joining Tables & Linking Tables = Headache

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
Contributor

Re: Joining Tables & Linking Tables = Headache

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
Contributor III

Re: Joining Tables & Linking Tables = Headache

can you give me the sample xls?

philmywallet
Contributor

Re: Joining Tables & Linking Tables = Headache

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
Contributor

Re: Joining Tables & Linking Tables = Headache

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
Contributor III

Re: Re: Joining Tables & Linking Tables = Headache

see atttached

philmywallet
Contributor

Re: Re: Joining Tables & Linking Tables = Headache


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

philmywallet
Contributor

Re: Re: Joining Tables & Linking Tables = Headache

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


pic1.jpg

Community Browser