Date(Date#)) function yields many duplicates

[#1]

Hi all,

I am importing from an ODBC server orders with their request date.

I would like to plot this in a chart where the dimension is YYYYMM, whereas the request date in the ODBC field is YYYYMMDD.

Therefore I wrote in the script

Date(Date#(LRDTE, 'YYYYMMDD'),'YYYYMM') AS RqstDte

It turns out fine and dates are imported as YYYYMM.

But, if I look in the list box, all the YYYYMM values are repeated as many times as the number of orders connected to them.

Further, when I plot the chart, of course it displays all these repeated months in the dimension.

It seems like that the Date(Date#)) function formats correctly the date, but indeed keeps stored the 'DD' information hidden somewhere.

Which formula shall I use to have QV sees the dates univocally?

Thank you for your help!

[brenner_martina]

Hi Spacetofu,

yes, that's right, because you only change the format not really the entry of the date. That means, that the complete date is "behind" the YYYYMM. Use the function Monthname or Year(date)&Num(Month(date),'00').

Greetings from Munich

Martina

[sparur · Accepted solution]

My be like this:

Date(MakeDate(Year(Date#(LRDTE, 'YYYYMMDD')),Month(Date#(LRDTE, 'YYYYMMDD'))), 'YYYYMM') AS RqstDte

[whiteline]

Both date functions return dual type variable with numeric and text representation.

Date# - gets string and coverts it to dual (with the same text format).

Date - changes only text represenataion (format).

I guess, you should trancate your LRDTE and use Date#(LRDTE, 'YYYYMM') first.

[#5]

If I understood right your approach, I simply wrote

Date#(LRDTE, 'YYYYMM') AS RqstDte

but still it returned dates as YYYYMMDD, sorry, not working!

[#6]

Dear Martina,

thank you for your explanation of the causes of the error.

Your suggestion seemed ok if I look barely at the script, but in the end it returned dates as

5698612

and repaeated values.

I tried to change Number override document setting

date>>YYYY-MM

But still I got a weird format like

17502-04

and, as I said, repeated dates.

but thanks for your inputs!

Schöne Grüße!

[brenner_martina]

Hi,

you can use this script:

Table2:

LOAD Distinct  Year(date)&Num(Month(date),'00')

Resident Table1;

Greetings from Munich

Martina

[#8]

When I was reading this, it seemed just so right.

With that MakeDate command, it seemed I could achieve jsut what I wanted, a "brand new" field which contains just 6 characters YYYYMM.

Indeed it is like this, flawless!

thank you!

[whiteline]

Hi spacetofu,

First trancate the string so that in contains only YYYYMM then use Date#.

[#10]

I did find this to work for an Good Receipt Date that was coming in from database as formatted like this '20130115' or YYYYMMDD.  QV surely did recognize it as a string and the code above from Anatoly worked.