Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

How to recode Due Date into year ?

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;

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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]

View solution in original post

7 Replies
vishsaggi
Champion III
Champion III

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
Partner - Creator III
Partner - Creator III

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
Master
Master
Author

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
Master
Master
Author

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
Champion III
Champion III

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
Partner - Creator III
Partner - Creator III

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.

Naamloos.png

paulyeo11
Master
Master
Author

Hi Roland

Thank you very much.

Paul Yeo