How to recode Due Date into year ?

[paulyeo11]

Hi All

I have the QVW file , it work fine on 18 may , but today when i try to run again , the calendar not working.

For exmaple year field display blank value.

Below is my load script , Hope some one can help me spot where it the error ?

Enclosed the Raw data and QVW file here.

Test:

LOAD Subject,

     [Sales Order],

     [Customer No],

     [Invoice No],

     [Contact Name],

     [Invoice Date],

     [Due Date],

     [Purchase Order],

     Adjustment,

     [Excise Duty],

     [Sub Total],

     [Sales Commission],

     Total,

     [Tax Type],

     [Discount Percent],

     [Discount Amount],

     [Organization Name] as [Organization Name],

Purgechar( [Organization Name], 'Accounts:::' ) as [Organization Name_],

     Status,

     [Assigned To],

     [Created Time],

     [Modified Time],

     Currency,

     [Conversion Rate],

     [Last Modified By],

     [Pre Tax Total],

     [S&H Amount],

     [Paid Time],

     [Billing Address],

     [Shipping Address],

     [Billing PO Box],

     [Shipping PO Box],

     [Billing City],

     [Shipping City],

     [Billing State],

     [Shipping State],

     [Billing Postal Code],

     [Shipping Postal Code],

     [Billing Country],

     [Shipping Country],

     [Terms & Conditions],

     [Item Name],

     Quantity,

     [List Price],

     [Item Comment],

     Discount,

     [Item Discount Percent],

     Tax1,

     Tax2,

     Tax3,

     Description

FROM

[Invoice.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Test2:

LOAD * ,

MonthName(Date#([Due Date],'DD-MM-YYY'))  as  MonthYear,

MonthName(Date#([Due Date],'DD-MM-YYY'))  as  YearMonth,

DATE((Date#([Due Date],'DD-MM-YYY'))) as [DATE],

num(month((Date#([Due Date],'DD-MM-YYY')))) as [month],

year((Date#([Due Date],'DD-MM-YYY'))) as [year],

(Date#([Due Date],'DD-MM-YYY'))  as DueDate1

Resident  Test ;

DROP Table Test;

[vishsaggi · Accepted solution]

Your Due Date format is DD/MM/YYYY so just change all the calendar fields format to DD/MM/YYYY.

Like

Year(Date#([Due Date], 'DD/MM/YYYY')) AS [YEAR]

[RonaldDoes]

Hi Paul,

If this ever worked, the date format in your source CSV has changed since then.

I've changed your calendar script into:

Test2:
LOAD * ,
MonthName(Date#([Due Date],'D/M/YYYY'))  as  MonthYear,
MonthName(Date#([Due Date],'D/M/YYYY'))  as  YearMonth,
DATE((Date#([Due Date],'D/M/YYYY'))) as [DATE],
num(month((Date#([Due Date],'D/M/YYYY')))) as [month],
year((Date#([Due Date],'D/M/YYYY'))) as [year],
(Date#([Due Date],'D/M/YYYY'))  as DueDate1
Resident  Test ;

Hereby a working version.

With kind regards,

Ronald

[paulyeo11]

Hi Vish

Your solution work , thank you very much.

By the way can you share with me , why the data format suddenly changes ? What are the reasons ?

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>

[paulyeo11]

Hi Ronald

Your also work , can i know how to paste those script into the body of your reply , how you make it look like coding ? i mean beside the script , it have line number on the first column. pls share with me how you do it ?

Also why last time it work , and what situation can make the date format change ?

[vishsaggi]

I am not quite sure how your data source changes. So when i looked in for your Due Date it shows date in D/MM/YYYY. Was this different few days ago?

May be we can use below if we do not worry about which format the date is coming we can change the expression accordingly. So something like below:

Eg:

Year(Date(Num(Floor([Due Date])), 'MM/DD/YYYY')) AS DueDateNum,

OR

Year(Date(Num(Floor([Due Date])), 'DD/MM/YYYY')) AS DueDateNum

[RonaldDoes]

Hi Paul,

What changes the format of the dates in your CSV is probably up to something in your source system. Could be something as small as an update, or the use of a different (Windows) user with different locale settings.

Showing code is done using the "advanced editor" and clicking Syntax highlighting -> (for example) PHP.

[paulyeo11]

Hi Roland

Thank you very much.

Paul Yeo