topic Re: Data Model Question in QlikView

Data Model Question

israrkhan — Tue, 18 Mar 2014 08:30:57 GMT

Hi Community,

i have a calculating problem, have spent hours, but no luck..

Your Thoughts will be appreciated,

PartsPerformance: (Table)

As you can see in the PartsPerformance Table, i hvae NetValue, can have Multiple values on daily basis.

and i Have A Month Field, in the format of Jan, Feb, Mar, Apr...and so...

NST_Targets1: (Table)

in NST_Targets1 Table, i have NST_Targets1.NSTMonth field in the format Jan, Feb , Mar, Apr.. so on..

and NST_Targets1.Targets field, which has Monthly Targets.one value for each month

OutPut: in Bar chart i added 1 Dimension Month, from PartsPerformance table

AND two expressions 1) Sum(NetValue), 2) sum(NST_Targets1.Targets)

Problem :

i can not see NST_Targets1.Targets Correctly, it repeats same value for all months. even i have targets for All Months.

but sum(netValue) showing correct.

when i Removed Month from dimension and i added NST_Targets1.NSTMonth as dimension, now i can see Targets correctly, and sum(net value) goes wrong.

Stucked....Please share your thoughts...

Thanks And Regards

Khan

Re: Data Model Question

Tue, 18 Mar 2014 08:34:05 GMT

maybe try renaming month columns so that they both have the same name

Re: Data Model Question

israrkhan — Tue, 18 Mar 2014 08:42:04 GMT

did not get you exactly,

did you mean, Rename the NST_Targets1.NSTMonth as Month..

so it will create a synthetic key?

Thanks

Israr

Re: Data Model Question

Tue, 18 Mar 2014 08:44:59 GMT

yeah exactly

maybe someone else can help you fix this without having to create synthetic key but I would try this just to see if it works and if you get the desired data...

Re: Data Model Question

israrkhan — Tue, 18 Mar 2014 08:46:36 GMT

Zainab let me try...

but never like Synthetic Keys...

Re: Data Model Question

martinpohl — Tue, 18 Mar 2014 08:48:27 GMT

so rename and concatenate load the table NST_Targets1 with PartPerformance.

You will get one table within the fields month, dealer and all other field which are the same in both tables.

e.g.

concatenate (PartPerformance) load

NST_Targets1.NSTMonth as Month,

NST_Targets1.Category as Category,

NST_Targets1.NSTCountry as Country

and than you can sum(targets) and sum (net value) per month.

Regards

Re: Data Model Question

Tue, 18 Mar 2014 08:48:58 GMT

I don't mind them, I try to avoid them but one or two don't have that much affect on my current data

Re: Data Model Question

PrashantSangle — Tue, 18 Mar 2014 08:49:42 GMT

Hi,

Try this as your dimension instead of month

=Only({<Month=p{ NST_Targets1.NSTMonth}>}Month)

Re: Data Model Question

martinpohl — Tue, 18 Mar 2014 08:49:44 GMT

the synthetic key has a problem.

if there are targets but no net value for one dealer / month you won't get the target values in your chart.

Re: Data Model Question

Tue, 18 Mar 2014 08:51:59 GMT

oh yes of course, I have never had that issue as I have only created synthetic key where there needs to be value in both columns so I didn't think of that....

Re: Data Model Question

israrkhan — Tue, 18 Mar 2014 08:59:33 GMT

Martin,

i think concatenate is not a solution here , because in parts performance i have around 25 columns, and In target only 5 columns.

and the have same only Dealercode, nothing else...

but many thanks for your time...

Thanks and Regards

Israr

Re: Data Model Question

israrkhan — Tue, 18 Mar 2014 09:02:39 GMT

Error in calculated Dimension...

Re: Data Model Question

Colin-Albert — Tue, 18 Mar 2014 09:06:15 GMT

I would agree with Martin - Concatenate the target data into the parts performance data.

The Dealer, date & month fields etc. will have the same names as in the parts performance table, and the target value will be a new column. The charts then just sums the NetValue and sums the Target data and becomes simple.

Re: Data Model Question

israrkhan — Tue, 18 Mar 2014 09:06:40 GMT

Zainab...

its working.. but again don't like Synthetic Keys .

Appreciated your idea....

Re: Data Model Question

martinpohl — Tue, 18 Mar 2014 09:06:48 GMT

so make a mapping table between PartPerformance and NST_Targets1 within 3 columns:

month

dealercode

month&dealercode as MonthDealer

create a field month&dealercode as MonthDealer in both tables.

the link to the dealer table should be already there so you only need a month table (or better a year/month table, so you should join year&month&dealercode.

regards

Re: Data Model Question

israrkhan — Tue, 18 Mar 2014 09:09:48 GMT

Albert thanks for your input,

But idea given by Zainab is working,

so shal i continue with this.. or concatenate will be better solution..?

Re: Data Model Question

Tue, 18 Mar 2014 09:12:48 GMT

what about a calendar table?

Re: Data Model Question

Anonymous — Tue, 18 Mar 2014 09:14:37 GMT

I would also recommend Martin's solution. You need to add the time dimension to key.

Serhan

Re: Data Model Question

Colin-Albert — Tue, 18 Mar 2014 09:18:24 GMT

I always concatenate target data to the measures table as it makes the charts simpler with less likelihood for introducing loops and/or synthetic keys. One advantage of concatenating the target data is that you can easily report on dealers who have a target but no sales in a period .

That said, if your chart is now working, it is up to you to decide whether re-working the data model is worth it.

To avoid the synthetic key, create a new key field that concatenates the DealerCode & Month fields

Re: Data Model Question

CELAMBARASAN — Tue, 18 Mar 2014 09:20:07 GMT

In NST_Targets1

Remove the DealerCode Field

Instead form a composite key

DealerCode&NstMonth AS DealerCodeMonth

And in PartsPerformance table

Add a field as below

DealerCode&Month AS DealerCodeMonth