topic Re: Re: Re: Joining Tables & Linking Tables = Headache in QlikView

Joining Tables & Linking Tables = Headache

Anonymous — Tue, 10 Sep 2013 10:45:35 GMT

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

Re: Joining Tables & Linking Tables = Headache

kedar_dandekar — Tue, 10 Sep 2013 11:15:43 GMT

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,

Re: Joining Tables & Linking Tables = Headache

Anonymous — Tue, 10 Sep 2013 11:20:40 GMT

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

Re: Joining Tables & Linking Tables = Headache

kedar_dandekar — Tue, 10 Sep 2013 11:35:42 GMT

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

Re: Joining Tables & Linking Tables = Headache

michael_maeuser — Tue, 10 Sep 2013 11:37:39 GMT

can you give me the sample xls?

Re: Joining Tables & Linking Tables = Headache

Anonymous — Tue, 10 Sep 2013 11:47:29 GMT

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

Re: Joining Tables & Linking Tables = Headache

kedar_dandekar — Tue, 10 Sep 2013 12:16:15 GMT

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

Re: Re: Joining Tables & Linking Tables = Headache

michael_maeuser — Tue, 10 Sep 2013 12:27:46 GMT

see atttached

Re: Re: Joining Tables & Linking Tables = Headache

Anonymous — Tue, 10 Sep 2013 12:43:21 GMT

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

Re: Re: Joining Tables & Linking Tables = Headache

Anonymous — Tue, 10 Sep 2013 12:53:17 GMT

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

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

michael_maeuser — Tue, 10 Sep 2013 13:16:16 GMT

sorry, was a bit confused...

that should help

Re: Joining Tables & Linking Tables = Headache

Anonymous — Tue, 10 Sep 2013 17:40:14 GMT

Finally managed to apply this to my data model.

It worked perfectly!! Thankyou.

If you get chance please could you explain your methods so I can learn from it and apply it to other reports in the future.

Thanks again

Phil