topic Re: Removing synthetic table in QlikView

Removing synthetic table

Mon, 27 Aug 2012 19:30:15 GMT

Not sure if someone can help me by just looking at the model I created but I want to remove the synthetic table somehow.

It comes about when I bring in my master calendar. My other tables don't have a general "date" field so I used the Makedate() function and the field "CreatedDate" in each table to generate a date and call it Calendar.Date.

Any help would be great.

Re: Removing synthetic table

Oleg_Troyansky — Mon, 27 Aug 2012 23:19:06 GMT

It looks like all those date fields are different, they all represent different events. That means, that they need to be named differently. That will resolve the problem of the synthetic keys, but create another problem - you can't use a single Master Calendar anymore.

I don't know much about your business requirements, but generally speaking, you need to pick one date as the "main" date and link it to the Master Calendar, while other dates can be "secondary" and you can populate a subset of calendar fields (such as Year, Month) just for them as needed.

For example, think of this - when the user selects Year and Month on top of the screen, what data do you want to get sleected - Accounts created in the selected Month? Or Opportunities? Or Quotes? You can't say "all of the above", because those are all different data sets. You have to pick one or another...

hope it helps,

Oleg Troyansky

Re: Removing synthetic table

Anonymous — Tue, 28 Aug 2012 01:27:01 GMT

How I'd deal with it:

Do not link Account Date and Contact Date to the calendar. It is unlikely that these Dates are really important. If needed, deal with it in front end expressions.

Concatenate Qoute, Opportunity, and Policy into one Fact table. It is connected only to Master Calendar and to Account (logical tables in bold):

Master Calendar - (Date) - Fact - (AccountId) - Account

(AccountId) - Contact

Regards,

Michael

Re: Removing synthetic table

Tue, 28 Aug 2012 12:46:39 GMT

What if I concatenate all the tables to the "Account" table and then call each "createddate" as "CreateDate". Then take that new "CreatedDate" field and use the makedate() function with it and call that "Calendar.Date"?

I tried it and it seems to work. Should there be any drawbacks to this?

Re: Removing synthetic table

Tue, 28 Aug 2012 13:01:04 GMT

and what would be the difference if I did what I said in my previous message with a left join instead of a concatenate?

Re: Removing synthetic table

Anonymous — Tue, 28 Aug 2012 13:01:24 GMT

It is correct technically (if the only purpose is to remive Sync) - it is wrong logically. Read again the post from Oleg. The question is - what you expect to see when you select August 2012? With the new data model, you get accounts and contacts created in this month, and Opportunities/Quotes/Policies for this month as well. You cannot answer a simple question like "show me Opportunities created in August 2012 by any Account", because the Accounts will be restricted to these create in August 2012 as well. I doubt that this is what you need.

Regards,

Michael

Re: Removing synthetic table

Tue, 28 Aug 2012 13:13:47 GMT

I was trying to provide the user with he least clicks possible to do a data reduction but what you're saying is I can choose less clicks but get less dynamic data reduction. Or have more clicks and get more dynamic data reduction.

Re: Removing synthetic table

Tue, 28 Aug 2012 20:23:40 GMT

Your suggestion would be like the following sample. Where Account and Contact are by themselves, and would have their own date fields.

Opportunity, Quote, and Policy are concatenated into one table and linked with the master calendar by the CreatedDate.

Re: Removing synthetic table

Anonymous — Wed, 29 Aug 2012 00:56:50 GMT

Yes, this is what I mean. hope it will work for you.

Regards,

Michael

Re: Removing synthetic table

Wed, 29 Aug 2012 06:50:35 GMT

Can you give this a try.

1. Create a Master Calendar (with all the Year, Month, Day, etc. using autogenerate).

2. Dont link this to any of the tables in the data model.

3. Create similar Year, Month and Day fields in all the tables, respectively.

4. Now in the frontend in your expressions, you can use and if condition (set analysis preferred as it is faster) to compare the dates with the ones from the Master Calendar, for the respctive reports, so you get the desired output.

Hope this helps,

Best Regards,

Robinson

Re: Removing synthetic table

Anonymous — Wed, 29 Aug 2012 10:16:25 GMT

Robinson,

I use this occasionally as well. The drawback here is the front-end performance, hence the solution is acceptable for a comparatively small data sets. That may be the case here, we don't know.

Regards,

Michael

Re: Removing synthetic table

Wed, 29 Aug 2012 11:39:17 GMT

Can you give me an example of a set analysis equation to do this? I'm not very familiar with syntax and process of it.

Thanks for all your help throughout this.

Re: Removing synthetic table

Wed, 29 Aug 2012 18:47:53 GMT

For example, I attached what my ultimate goal will be for using this model. Kind of curious if you have any ideas on how to do this as well.

I wanted to try and do this with a funnel chart.

Where a Lead = a count of all Opportunities with a created date in the current year to date. (ie Opportunity.CreatedDate) Same goes for Last year to date.

Where a Quote = a count of all Opportunities with a Quote total (ie Quote.GrandTotal) > 0 and then referencing the Lead for everything current year to date and then last year to date.

So as you might see, leads = 20851 (YTD) in my report and then OF THOSE 20851 leads, 16829 then become Quotes. It all flows downward.

Any ideas as to how to put these buckets and formats into a funnel chart? I understand that YTD and LYTD will be in different graphs. Plus the variances will be too.