topic Re: Datamodel in QlikView

Datamodel

NavinReddy — Mon, 14 Apr 2014 15:41:43 GMT

Dear All,

I created Master Calendar

when i am connecting with this one excel data working fine

In excel i have only two months data ,highlighted only two months ,Here im getting only 12 Months

When i am connection with same location one more excel data,

it has Jan ,Feb ,Mar Data but its showing Jan,Feb,Mar including 12months data(Jan,Feb,Mar+12Months)

Please help me some one

Thanks In Advance

Niranjan

Re: Datamodel

MK_QSL — Mon, 14 Apr 2014 15:46:58 GMT

Not understood properly from your description.. Can you please elaborate little bit more ?

Re: Datamodel

its_anandrjs — Mon, 14 Apr 2014 15:52:27 GMT

It is due to your Master calender as i under stand try to plot all date dimension like

Year, Month and then check your data which is only 3 months data plot any sum value and check if there is no sum value for rest of the month it shows zero value for that.

Let me know

Regards

Re: Datamodel

NavinReddy — Mon, 14 Apr 2014 15:57:05 GMT

Hi Manish,

I have two excels Data i have

first excel sheet i have 2 months data Jan,feb ,here i created master calendar and connected with master calendar here im getting two months data with highleghting

Second excel sheet i have 3 months data Jan,Feb,Mar ,here when i trying to connect with Master calendar

im getting Jan,Feb,Mar +12months

When i select Jan First Excel data im not getting

When i select 12 moths Jan im not getting Second Excel Data

Please give me an idea

Best Regards,

Niranjan

Re: Datamodel

NavinReddy — Mon, 14 Apr 2014 16:00:46 GMT

Hi Anand ,

I am not getting how can i fix this error

could you please explan little bit more

Best Regards,

Niranjan

Re: Datamodel

PrashantSangle — Mon, 14 Apr 2014 16:08:38 GMT

Hi,

just create one master calender table,

and from both excel table give same name for date field

and then pass that datefield in your master calender.

Regards,

Re: Datamodel

NavinReddy — Mon, 14 Apr 2014 16:09:16 GMT

Yes Anand,

When i use no concatination option

Month Data showing like (FEB,JAN,MAR)

like format It May be like this Error

Please any Suggessions

Regards,

Niranjan

Re: Datamodel

MK_QSL — Mon, 14 Apr 2014 16:16:07 GMT

Can you share your apps or sample data file?

Re: Datamodel

its_anandrjs — Mon, 14 Apr 2014 16:22:49 GMT

I think there is some file problem that you concatenate check your load script it may be like if you use common calender you need is to provide only date column from the tables

Eg:-

//suppose here is Jan, Feb data available

Tab1:

Load

Fieldname as Date ///common date field

From Location;

Concatenate

//And here you have Jan, Feb, Mar data is available

Tab2:

Load

Fieldname as Date ///common date field

From Location;

MasterCalender:

Load

Date,///common date field

Year,

Month

From Location;

After linking you get 12 months and year what ever year you have but data available here is only for Jan,Feb,Mar,Apr,May and do not link your table with any month field.

Regards

Re: Datamodel

NavinReddy — Mon, 14 Apr 2014 16:24:21 GMT

while loading how can change the format

Like jan ,feb,mar

month(date#(month)) as month ,

it will work

Re: Datamodel

MK_QSL — Mon, 14 Apr 2014 16:25:55 GMT

May be like this

Date(Date#(month,'MMM'),'MMM') as month

Re: Datamodel

its_anandrjs — Mon, 14 Apr 2014 16:26:51 GMT

Sorry i miss some thing

Write like

=Month(Date(Date#(month,'MMM'),'MMM'))

it will convert JAN,FEB,MAR TO Jan,Feb,Mar

Re: Datamodel

NavinReddy — Mon, 14 Apr 2014 16:35:32 GMT

Hi ,

i am atteching sample qvw please find the attechment

and here Month2 one excel sheet, Month one more excel,month1 master calendar

please help me

Regards,

niru

Re: Datamodel

its_anandrjs — Mon, 14 Apr 2014 16:51:28 GMT

If you not have common date field in the tables then use makedate command in the tables and then link it with the date field of the master calender

MakeDate(Year(Trim(Left([Pay Yr & Month],4))),month(date#(Trim(Right([Pay Yr & Month],2)), 'MM'))) as CreatedDate

in Occupancy table your Year field is not proper as year it is in text format please check this and then use makedate function for creating date field for connecting with the master calendar.

Regards

Re: Datamodel

MK_QSL — Mon, 14 Apr 2014 16:55:24 GMT

Your month field is coming from Pay Yr & Month which contains all Months from 2013 and Jan & Feb from 2014.

What exact problem you are facing. Still unclear from your question.

If the problem is that you are getting all months for year 2014... you can overcome the problem by joining your Fact Table with Master Calendar something like below..

Inner Join (OverTime) Load * Resident MasterCalendar;

Drop Table MasterCalendar;

Considering that Month is the common field here.

Another solution is to find out Min and Max Month from your OverTime table and to generate data for master calendar.

Hope this helps...

Re: Datamodel

NavinReddy — Mon, 14 Apr 2014 18:02:10 GMT

Thanks you guys your reply is very help full

Thank you so much