Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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]
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]
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
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>
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 ?
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
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.
Hi Roland
Thank you very much.
Paul Yeo