topic Re: Calendar/date issue - please help! in QlikView

Calendar/date issue - please help!

alexis — Thu, 09 Aug 2012 14:21:08 GMT

I have attached a QVW that has a simplified view of my problem.

The QVW has a data file "Welfare payments" and a "Calendar" which I generate to offer rich calendar-type functionality

The 2 tables are linked by a field called "PaymentDate"

For some reason, none of the selections from a Calendar field take me to the relevant records. This is driving me insane. Any suggestions.

Regards and thanks in advance

Alexis

Re: Calendar/date issue - please help!

giakoum — Thu, 09 Aug 2012 14:32:57 GMT

Καλησπέρα Αλέξη.

υπάρχει περίπτωση να έχουμε κανένα ελληνικό Μ ή Υ στο 'D/M/YYYY'?

Re: Calendar/date issue - please help!

alexis — Thu, 09 Aug 2012 14:38:41 GMT

Good suggestion Ioanni.

In fact,to make sure that there are no Greek characters in the 'D/M/YYYY' I changed the code slightly to make sure that we are formatting the raw data:

WelfarePayments:

LOAD WPayIdentity,

WName,

WPayAddress,

WPaymentDate,

Date(WPaymentDate, 'D/M/YYYY') As PaymentDate,

WRunDate,

WFormattedPaymentDate,

// Date(WFormattedPaymentDate, 'D/M/YYYY') As PaymentDate,

WAmount,

CATEGORY,

WPaymentId,

WNationality,

WComment,

WGrantCode

FROM

QVD\WelfarePaymentsCurrentSAMPLE.qvd

(qvd);

Re: Calendar/date issue - please help!

giakoum — Thu, 09 Aug 2012 14:53:53 GMT

Month(WFormattedPaymentDate) and month(PaymentDate) match exactly and work as a filter quite nicely, so there must be a mistake in the expression of month in the calendar.

If you take Month (from calendar) in a straight table, it shows null, while the above expressions show month name just the same.

Try, instead of selecting M AS Month in the calendar, to select month(D) AS Month. I know it is done already in the DateIsland but maybe it helps.

Re: Calendar/date issue - please help!

alexis — Thu, 09 Aug 2012 15:01:43 GMT

Ioannis Giakoumakis wrote:
Month(WFormattedPaymentDate) and month(PaymentDate) match exactly and work as a filter quite nicely, so there must be a mistake in the expression of month in the calendar.
If you take Month (from calendar) in a straight table, it shows null, while the above expressions show month name just the same.
Try, instead of selecting M AS Month in the calendar, to select month(D) AS Month. I know it is done already in the DateIsland but maybe it helps.

Hi Ioannis

Yes I am aware that Month(WFormatedPaymentDate) and month(PaymentDate) match exactly - that was the purpose of my demo application; this is not surprising as both of these fields, in essence, reside in the same table (the payments table). What buffles me is the fact that the PaymentDates of the Calendar and Payments appear to not be the same (although they look the same)...

Re: Calendar/date issue - please help!

giakoum — Thu, 09 Aug 2012 15:16:42 GMT

Hi Alexis

PaymentDates are the same, at least in the sample you posted, but month, as created in the calendar is not the same. I think that the month creation is the problem, but cannot test it as I cannot reload.

Είσαι ακόμα Κύπρο? Ελπίζω να είσαι καλά!

Re: Calendar/date issue - please help!

rwunderlich — Thu, 09 Aug 2012 15:20:36 GMT

WFormattedPaymentDate has a time portion in it, which you are not removing, so the PaymentDate in the WelfarePayments retains the time portion and does not match the Calendar table.

The Date() function is a formatting function, it does not remove the time. Add a floor() function to fix.

Date(floor(WFormattedPaymentDate), 'D/M/YYYY') As PaymentDate

-Rob

http://robwunderlich.com

Re: Calendar/date issue - please help!

alexis — Thu, 09 Aug 2012 15:26:52 GMT

Hi all,

I have 2 QVW applications, one that acts as QVD generator

Still in Cyprus trying to keep my head above water!

The idea of generating the calendar and linking it with the payments data is to benefit from ALL the fields that I have built in the feature-rich calendar, so it is imperrative that the 2 tables are properly linked. I will try and attach the QVD so that you can re-run it.

Much appreciate your help mate!

Re: Calendar/date issue - please help!

giakoum — Thu, 09 Aug 2012 15:33:41 GMT

yeap, I have to admit, there is no one like Rob...

Alexis, if you replace your expression with Robs, it works like a charm!

I have tested and reloaded it.

Thank you Rob, excellent!

Re: Calendar/date issue - please help!

alexis — Thu, 09 Aug 2012 15:35:32 GMT

Hi Rob

That IS the correct answer and I thank you for that.

How could you tell that the "WFormattedPaymentDate" had a time element to it - just by prevewing the table shows it as d/m/y - the only clue that I can see is that "WpaymentDate" (which is the raw version of "WFormattedPaymentDate") is shown as 40960,12345 (which I am guessing the integer part is the date part and the decimal part the time)?

Thanx again

Alexis

Re: Calendar/date issue - please help!

rwunderlich — Thu, 09 Aug 2012 16:22:22 GMT

That's correct. The decimal part is the time. I put WFormattedPaymentDate in a list box and changed the number format to timestamp (or fixed number of decimal places).

-Rob

Re: Calendar/date issue - please help!

alexis — Thu, 09 Aug 2012 16:24:10 GMT

Thanx Rob - that was very helpful!