topic Re: Multiple dates fields in QlikView

Multiple dates fields

Tue, 05 Aug 2014 21:27:40 GMT

Hi,

I'm a new in qlikview and do my first project.

I've created database for fictitious web store with randomized records.

I'm stuck in financial raports.

I'v done tables like income, salaries etc.

However, I can't do a table like "profit", because I've different date source for each field.

I was trying make common calendar like here: Tutorial - Using Common Date Dimensions and Shared Calendars

Unfortunately, my dates aren't lineked on one field, so I cant use these method.

I have tried hadle with this problem for several days and currently I'm out of any ideas.

That's why I ask you for help.

I've attached my project.

Regards,

Maciej

Re: Multiple dates fields

Chip_Matejowsky — Tue, 05 Aug 2014 21:38:42 GMT

Hello Maciej,

It appears you are requesting assistance with QlikView. This is the Qlik Sense forum. Please post your question to the appropriate QlikView forum. Thank you.

Re: Multiple dates fields

Josh_Good — Tue, 05 Aug 2014 21:43:52 GMT

I'll move the discussion for you.

-Josh

Re: Multiple dates fields

jaimeaguilar — Tue, 05 Aug 2014 21:58:20 GMT

Hi,

you can use makedate() function in order to get a complete date that you can use to create a master calendar (or as many as you need.

For example in your case you can create a new field like this:

MakeDate(ORDERS.order_date_Year, ORDERS.order_date_month, ORDERS.order_date_day)

Also this field can serve as basis for creating a master calendar,

regards

Re: Multiple dates fields

Tue, 05 Aug 2014 22:20:54 GMT

Hi,

I advice you think in a star schema with a fact table created with Orders and Order_Details (you can use a Left Join, first load Order and then left join Order_Details), I guess your main Date Field must be created with MakeDate() Function using Year,Month and Day Fields (I guess Order_Date_Year/Month or Day) and based on this calculated field create a Master Calendar.

Something like this:

Orders:

LOAD

ID_Order,

ID_Employee,

ID_Customer,

Date(

MakeDate(Order_Date_Year,Order_Date_Month,Order_Date_Day),'YYYY-MMM-DD') as OrderDate,

... // Rest of your Fields

From Orders.qvd;

LeftJoin(Orders)

Order_Detail:

LOAD

ID_Order,

ID_Product,

Amount

From Order_Details.qvd;

With above script you can create your fact table and then just use Order date to create Master Calendar because your QV app is for analysing Sales so the main Date Field is Order Date, the others Dates in the rest of your table are atributes not a representative Dimension like Orde Date.

MasterCalendar:

LOAD Distinct

OrderDate,

Year(OrderDate) as Year,

Month(OrderDate) as Month,

Day(OrderDate) as Day,

Week(OrderDate) as Week,

MonthName(OrderDate) as Period

Resident Orders;

The rest of your table are Dimensions and others are Atributes of that tables, tomorrow I try to give you your QVW with this.

Best regards.

Re: Multiple dates fields

Wed, 06 Aug 2014 08:58:36 GMT

Hi Oswaldo,

Thanks for your response.

Unfortunately, this doesn't solve my problem.

In my table "Profit" I need calculate something like that: Sales - (Supplies + Salaries)

Each of them has different sources of dates(Sales - Orders.end_date, Supplies - Supply_date, Salaries - Salary.Date ).

I think it needs to combine it into a single calendar, but I don't know how to do that.

Regards,

Maciej

Re: Multiple dates fields

Wed, 06 Aug 2014 15:13:25 GMT

HI,

Well if you need to take Date Fields in Orders and Date Fields in Supplies you can try to rename that fields in both tables (Orders and Supplies) with the same Name and concatenate two tables. Something like this:

Orders:

LOAD

ID_Order,

ID_Employee,

ID_Customer,

Date(

MakeDate(Order_Date_Year,Order_Date_Month,Order_Date_Day),'YYYY-MMM-DD') as MainDate,

... // Rest of your Fields

From Orders.qvd;

LeftJoin(Orders)

Order_Detail:

LOAD

ID_Order,

ID_Product,

Amount as OrderAmount

From Order_Details.qvd;

Concatenate(Orders)

Supplies:

LOAD

ID_Product,

ID_Supplier,

ID_Supply,

Amount as SupplyAmount,

UNIT_PRICE,

Date(

MakeDate(Supply_Date_Year,Supply_Date_Month,Supply_Date_Day),'YYYY-MMM-DD') as MainDate

From Supplies.qvd;

Following this way you get a table with a single date Field created by Oredr_Dates and Supply Dates and you be able to use in the same table OrderAmount and SupplyAmount.

MasterCalendar:

LOAD Distinct

MainDate,

Year(MainDate) as Year,

Month(MainDate) as Month,

Day(MainDate) as Day,

Week(MainDate) as Week,

MonthName(MainDate) as Period

Resident Orders;

Above Master Calendar has Order Date Fields and Supply Date Fields as single Main Date.

I hope it will be usefull for you.

Best regards.

Re: Multiple dates fields

PrashantSangle — Wed, 06 Aug 2014 15:21:24 GMT

Hi,

Try to maintain same format for all dates then rename it to one common name

and then passed that name in Master Calender.

And then use that master calender date for your calculation

Regards

Re: Multiple dates fields

Wed, 06 Aug 2014 20:07:15 GMT

Hi,

I've tried do it like that, but it dosen't work.

After raneme date to common name my schema looks like that:

I have error OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocating 16MB

and QlikView stop working.

What can I do to correct it?

Regards,

Maciej

Re: Multiple dates fields

Wed, 06 Aug 2014 20:17:01 GMT

Hi,

You have to Join Order Details to Orders to get One Table Orders (With Orders and Order_Details) then concatenate with Supplies (previous you rename Date Fields in Orders and Supplies) finally Create a Master Calendar based on Table Orders (With Order, Order_Details and Supplies). The order of this load is very important... if you have problems give 10 minutes and I'll share you the qvw with this.

Careful in your image I can see you don't Left Join Order_Details and don't concatenate Supplies to Orders just Rename Fields, your script have to be like this:

Orders:

LOAD

ID_Order,

ID_Employee,

ID_Customer,

Date(

MakeDate(Order_Date_Year,Order_Date_Month,Order_Date_Day),'YYYY-MMM-DD') as MainDate,

... // Rest of your Fields

From Orders.qvd;

LeftJoin(Orders)

Order_Detail:

LOAD

ID_Order,

ID_Product,

Amount as OrderAmount

From Order_Details.qvd;

Concatenate(Orders)

Supplies:

LOAD

ID_Product,

ID_Supplier,

ID_Supply,

Amount as SupplyAmount,

UNIT_PRICE,

Date(

MakeDate(Supply_Date_Year,Supply_Date_Month,Supply_Date_Day),'YYYY-MMM-DD') as MainDate

From Supplies.qvd;

MasterCalendar:

LOAD Distinct

MainDate,

Year(MainDate) as Year,

Month(MainDate) as Month,

Day(MainDate) as Day,

Week(MainDate) as Week,

MonthName(MainDate) as Period

Resident Orders;

Best regards.

Re: Multiple dates fields

Wed, 06 Aug 2014 23:00:48 GMT

Hi,

Thanks for your great help.

I have a problem with this script(I can't Join Order Details to Orders).

Can you show how it should look?

Regards,

Maciej

EDIT: Ok, I handle it. It works great. Thank you for your help.