Dates issue

[dseelam]

Hello All,

I am having this strange issue

Created dates depending on shipment_date

Please check Attached files, some how its missing ShipDate for only 4/1 to 4/9, 5/1 to 5/9, 6/1 TO 6/9, 7/1 TO 7/9, 8/1 TO 8/8 OF 2016

Totally it has dates from 4/1/16 to 8/8/16

please help

Thanks in advance

[sunny_talwar · Accepted solution]

Try with this:

SET ShipmentDate = date#(SHIPMENT_DATE,'MM/DD/YYYY h:mm');

[dseelam]

Thanks Sunny

[dseelam]

Sunny,

I have an other file attached to original post using the same code you sent me except using mcks*.xls] in from statement but doing so not bringing dates from 8/9 to 8/21 in new file I attached

SET ShipmentDate = date#(SHIPMENT_DATE,'MM/DD/YYYY h:mm');

LOAD SHIPMENT_DATE,
Date(floor($(ShipmentDate))) as ShipDate,
num(floor($(ShipmentDate))) as ShipDateNum,
Month(floor($(ShipmentDate))) as ShipDateMonth,
Day(floor($(ShipmentDate))) as ShipDateDay,
Year(floor($(ShipmentDate))) as ShipDateYear,
date(monthstart($(ShipmentDate)),'MMM YYYY') as ShipDateMonthYear,
// ShipDate,
   [sum(GROSS_SALES)]
FROM

(biff, embedded labels, table is Sheet1$);

[sunny_talwar]

The new file you attached is a csv file. Is it even loading?

[dseelam]

Yes it worked out I think I saved in CSV and Xls files