7 Replies Latest reply: Jun 19, 2017 9:10 AM by Yeo Poh sai RSS

    How to recode Due Date into year ?

    Yeo Poh sai

      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;