topic Many to many relation data loading issue in QlikView

Many to many relation data loading issue

sridhar_sigired — Thu, 03 Mar 2016 06:46:06 GMT

Hi All,

I have a FACT Table with below information.

DateUID	Sales
1	1000
1	1000
1	156
1	26
1	258
2	285
3	100
3	1458
3	265
4	2558
4	1245
4	2655
4	254

Date dimension table line below

DateUID	Date	TimeSlotHour	Time Zone PST Date	Time Zone IST Date
1	3/3/2016	1	3/3/2016	3/4/2016
2	3/3/2016	2	3/3/2016	3/4/2016
3	3/3/2016	3	3/3/2016	3/4/2016
4	3/3/2016	4	3/3/2016	3/4/2016
5	3/3/2016	5	3/3/2016	3/4/2016
6	3/3/2016	6	3/3/2016	3/4/2016
7	3/3/2016	7	3/3/2016	3/4/2016
8	3/3/2016	8	3/3/2016	3/4/2016
9	3/3/2016	9	3/3/2016	3/4/2016
10	3/3/2016	10	3/3/2016	3/4/2016
11	3/3/2016	11	3/3/2016	3/4/2016
12	3/3/2016	12	3/3/2016	3/4/2016
13	3/3/2016	13	3/3/2016	3/4/2016

I need to link DateUID.... when i am linking it is Many to One relation (Fact to Dim), that is fine.

However i am dividing Date dimension table based on Timezones (like PST and IST) and concatenating them... so that i will show all timezone in a list box for selection.

Now that problem is it is many to many relation and while loading... it is taking much much time... of course i dont even know whether it will work out or not.

Can anyone suggest how can we fulfill this requirement?

Thanks in advance.

Re: Many to many relation data loading issue

Anonymous — Thu, 03 Mar 2016 07:03:10 GMT

I would suggest do the grouping in fact table and link it to your DIM table which would be 1:1 relationship like this?

FactTable:

Load

DateUID,

Sales

From FactTable;

noconcatenate

FinalFact:

load

DateUID,

sum(Sales) as Sales

resident FactTable

group by DateUID;

drop table FactTable;

Now FinalFact would be link to Dim Table into 1:1, hope this will help!!

Re: Many to many relation data loading issue

sridhar_sigired — Thu, 03 Mar 2016 07:25:56 GMT

Due to concatenation of my Dimensional Data table for time zone wise, it is also showing each UID many times (like 8 time zones are there, i am concatenating 8 times, so 8 same UIDs).... it will be 1:M then...

Apart from this grouping any other suggestion... if i do grouping, majority of my KPIs need to modify... they are based on count()...

Re: Many to many relation data loading issue

Anonymous — Thu, 03 Mar 2016 07:35:54 GMT

It will be 8 times only as with Each UID with different Timezone will make a unique combination which I dont think that avoidable.

Let wait for some more opinion from other experts

Re: Many to many relation data loading issue

sridhar_sigired — Thu, 03 Mar 2016 12:09:10 GMT

Can anyone suggest me on this?

How can we achieve my requirement.

Re: Many to many relation data loading issue

MayilVahanan — Thu, 03 Mar 2016 12:21:25 GMT

Instead of concatenate for Timezone, you can use Join right?

Re: Many to many relation data loading issue

sridhar_sigired — Thu, 03 Mar 2016 12:28:50 GMT

Hi,

The DateTime Table is already joined, which we are dividing and concatenating.

If i use this joined table as it is, i cannot show the list of time zones, through which our calendar need to filter.

I want a combined calendar for all time zone, so that i can show one time zone with 'always one selected value' from list box.

So that end user can select whatever time zone he want.

Re: Many to many relation data loading issue

Anonymous — Thu, 03 Mar 2016 12:33:19 GMT

The attached qvw seems to work, but I am not sure about the combined calendar for all time zone you mention.

Could you clarify what is not working for you.

Re: Many to many relation data loading issue

sridhar_sigired — Fri, 04 Mar 2016 06:12:32 GMT

Hi Bill,

I have tested this logic with sample data before i implement with production data.

Yes, this is working.

I don't know the reason why is it not working with big data set...

Fact:

DateUID;

LeftKeep

DIM:

DateUID;

I am using left keep with key field... and after few hours it is getting failed...

Log is showing that keep/join error....

My Date Dimension have 2017 Dec31st, so thats why i am using left keep...

So that may be that reason...

How can i avoid this with huge data?

Thanks for your time Bill.

Re: Many to many relation data loading issue

Anonymous — Fri, 04 Mar 2016 08:07:38 GMT

Could you share the log file from your reload.

Re: Many to many relation data loading issue

sridhar_sigired — Fri, 04 Mar 2016 09:56:19 GMT

My Apologies Bill... Just understand that the problem is there in key field name, which was not correct...

This is solved and working fine. Thanks for your help.