topic Re: 2 Dates from the same table. Can I create a master calendar? in QlikView

2 Dates from the same table. Can I create a master calendar?

neena123 — Sat, 16 Nov 2024 04:26:49 GMT

Hello!!

I have read many documents and tried to figure out this myself but I need a little extra help...

So I have this table called Estimate. Now for every estimate put into the system there is an Entry Date also known as the "Bid Date" now once that Bid is accepted then there is a Reward Date. Both dates are located under the Estimate file

I am creating a report that has every detail on the estimate. I would like for my end user to have the ability to select the month Feb. and see all the Entry Dates made for that month as well as the older Entry Dates that now have Reward Dates.

What my table is doing now when I select Feb as the month, it shows All Entry Dates in Feb and Reward Dates in Feb. that has Entry Dates In Feb. For example the top image is what my report is doing now and below that is what I want it to do. Any help is appreciated!! Thanks!

What I want is below:

Re: 2 Dates from the same table. Can I create a master calendar?

rwunderlich — Thu, 14 Feb 2019 19:00:55 GMT

Please see this tutorial: Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

The downloads are available in both Qlik Sense and QlikView formats.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: 2 Dates from the same table. Can I create a master calendar?

neena123 — Thu, 14 Feb 2019 20:15:14 GMT

Hello!

I tried that and when I went to select Feb I was showing dates in the wrong months.

Re: 2 Dates from the same table. Can I create a master calendar?

neena123 — Thu, 14 Feb 2019 22:19:27 GMT

Do you have an idea as to why? The Common date that I created is making up a date filed that doesn't apply. You can see below the common date field has generated the date 2-13-2019 but the entry date and reward date do not have a date in Feb. So why is it displaying?

Re: 2 Dates from the same table. Can I create a master calendar?

rwunderlich — Fri, 15 Feb 2019 15:04:24 GMT

I can't tell from your screenshot. Can you upload a sample qvw?

-Rob

Re: 2 Dates from the same table. Can I create a master calendar?

neena123 — Fri, 15 Feb 2019 15:09:04 GMT

Hi Rob, Before I upload a sample qvw I just wanted to ask if the reason why it isn't working is because I am bringing in 2 QVDs then joining the files and then creating my master calendar. The reason why I have separate QVDs is because the data fields are the exact same but the values are different because of different sources.

Re: 2 Dates from the same table. Can I create a master calendar?

rwunderlich — Fri, 15 Feb 2019 16:33:33 GMT

Hard to say without seeing your script. Are you concatenating the QVDs or joining them?

Re: 2 Dates from the same table. Can I create a master calendar?

neena123 — Fri, 15 Feb 2019 16:37:44 GMT

[Estimate]:
LOAD estimate,
estimatepartid,
salespersonid,
description1,
estimatenumber,
description,
customerprospectname,
enteredby,
duedate,
entrytime,
entrydate,
estimaterequest,
estimateversionnumber,
estimatorid,
lastjob,
rewarddate,
estimatequantityid,
markup,
markuppercent,
cost,
nonvalueaddedmarkup,
nonvalueadded,
overallmarkup,
price,
quotedprice,
quantityordered,
valueaddedmarkup,
valueadded,
valueaddedprice,
valueaddedpercent,
arsalesname,
[Estimate Part]
FROM
[D:\Client View\PRT\PRTI.qvd]
(qvd);

JOIN(Estimate)
LOAD estimate,
estimatepartid,
salespersonid,
description1,
estimatenumber,
description,
customerprospectname,
enteredby,
duedate,
entrytime,
entrydate,
estimaterequest,
estimateversionnumber,
estimatorid,
lastjob,
rewarddate,
estimatequantityid,
markup,
markuppercent,
cost,
nonvalueaddedmarkup,
nonvalueadded,
overallmarkup,
price,
quotedprice,
quantityordered,
valueaddedmarkup,
valueadded,
valueaddedprice,
valueaddedpercent,
arsalesname,
[Estimate Part]
FROM
[D:\Client View\RPT\RPTI.qvd] (qvd);

DateLink:
LOAD Distinct
estimatenumber,
rewarddate as Date
Resident Estimate
;

Concatenate(DateLink)
LOAD Distinct
estimatenumber,
entrydate as Date
Resident Estimate ;
Calendar:
LOAD

Date,
Month(Date) AS Month,
Year(Date) AS Year,
Day(Date) AS Day
Resident DateLink;

Re: 2 Dates from the same table. Can I create a master calendar?

rwunderlich — Fri, 15 Feb 2019 17:09:31 GMT

Loading of RPTI should not be JOIN(Estimate). It should be:

CONCATENATE(Estimate)

CONCATENATE will append additional rows to the table which is what you want.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: 2 Dates from the same table. Can I create a master calendar?

neena123 — Fri, 15 Feb 2019 17:14:23 GMT

Thanks Rob but I tried that and it is giving me the wrong dates still