How to create a date field from another date field

[akpofureenughwu]

Hello everyone, I need assistance on a QV work.

I have a field on a QVD file and I am requested to create  another field date from the existing date field

in this relationship, New Date = 45 days + Posting date

Thanks

Please find the attached file.

[m_woolf]

In script:

date(PostingDate+45) as NewDate

[akpofureenughwu]

Hello sir,It didn't work.

[akpofureenughwu]

Please find the script in txt file for easier understanding.

[sunny_talwar]

Your Posting Date is not read as Date by QlikView. You would need to fix that issue before you can use it to create any other date. Try like this:

Table:

LOAD *,

          Date(PostingDate + 45) as NewDate;

LOAD Date(Date#([PostingDate], 'DD.MM.YYYY')) as PostingDate

          OtherFields

FROM Source;

Read more about dates here:

Get the Dates Right

Why don’t my dates work?

[sunny_talwar]

Did you look at what I proposed?

[akpofureenughwu]

@ Sunny. Thanks It's work but my date format has been interrupted. I need to produce day, month, quarter and year for this work. So can u help me with this?

Thanks

[sunny_talwar]

May be something like this:

LOAD *,

    Date(PostingDate + 45) as NewDate,

    Month(PostingDate) as Month,

    Year(PostingDate) as Year

    'Q' & Ceil(Month(PostingDate)/3) as Quarter,

    MonthName(PostingDate) as MonthYear,

    Dual('Q' & Ceil(Month(PostingDate)/3) & '-' Year(PostingDate), QuarterStart(PostingDate)) as QuarterYear;

LOAD [Account type - Account type Level 01 (Key)],

    [Account type - Account type Level 01 (Text)],

    [Chart of accounts - Chart of accounts Level 01 (Key)],

    [Chart of accounts - Chart of accounts Level 01 (Text)],

    [G/L Account - G/L Account Level 01 (Key)],

    [G/L Account - G/L Account Level 01 (Text)],

    [Base Date - Base Date Level 01 (Key)],

    [Base Date - Base Date Level 01 (Text)] as [BaseDate],

    [Net due date - Net due date Level 01 (Key)],

    [Net due date - Net due date Level 01 (Text)],

    [Company code - Company code Level 01 (Key)],

    [Company code - Company code Level 01 (Text)],

    [Vendor - Vendor Level 01 (Key)],

    [Vendor - Vendor Level 01 (Text)],

    [Document Date - Document Date Level 01 (Key)],

    [Document Date - Document Date Level 01 (Text)],

    [Document type - Document type Level 01 (Key)],

    [Document type - Document type Level 01 (Text)],

    [Item - Item Level 01 (Key)],

    [Item - Item Level 01 (Text)],

    [Document no. - Document no. Level 01 (Key)],

    [Document no. - Document no. Level 01 (Text)],

    [Item Text - Item Text Level 01 (Key)],

    [Item Text - Item Text Level 01 (Text)],

    [Posting date - Posting date Level 01 (Key)],

    Date(Date#([Posting date - Posting date Level 01 (Text)], 'DD.MM.YYYY'), 'DD-MM-YYYY') as [PostingDate],

    [Fiscal year/period - Fiscal year/period Level 01 (Key)],

    [Fiscal year/period - Fiscal year/period Level 01 (Text)],

    [Document currency - Document currency Level 01 (Key)],

    [Document currency - Document currency Level 01 (Text)],

    [Local currency - Local currency Level 01 (Key)],

    [Local currency - Local currency Level 01 (Text)],

    Amount,

    [Amount - Unit],

    [Credit amt in LC],

    [Credit amt in LC - Unit],

    [Debit amt in LC],

    [Debit amt in LC - Unit],

    [Debit/Credit Amount],

    [Debit/Credit Amount - Unit],

    [Discount base],

    [Discount base - Unit]

[akpofureenughwu]

Hello Sunny, I worked but a bit complicated for me, The text file attached to this mail is the original work I was on but I just couldn't create a new date from the posting date with this relationship

Newdate= Postingday+45 days

I have introduced your first solution to the script but it didnt run..

Can u look at this text file and effect changes?

Thanks in millions

[sunny_talwar]

Try this:

LOAD *,

     Day & ' ' & Month as _Month;

LOAD *,

     Match(Quarter,'Q1','Q2','Q3','Q4') as Quar;

LOAD *,

     Month(Date) as Month,

     WeekDay(Date) as WeekDay,

     'Week ' & Week(Date) as Week,

     Year(Date) as Year,

    'Q'& Ceil(Month(Date)/3) as Quarter,

    'Day '& Day(Date) as Day,

     Day(Date) as nDay;

LOAD *,

     Date(PostingDate + 45) as DueDate;

LOAD [Account type - Account type Level 01 (Key)],

     [Account type - Account type Level 01 (Text)] as [Account Type],

     [Chart of accounts - Chart of accounts Level 01 (Key)],

     [Chart of accounts - Chart of accounts Level 01 (Text)] as [Chart of accounts],

     [G/L Account - G/L Account Level 01 (Key)],

     [G/L Account - G/L Account Level 01 (Text)] as [G/L Account],

     [Base Date - Base Date Level 01 (Key)],

     [Base Date - Base Date Level 01 (Text)] as [Base Date],

     [Net due date - Net due date Level 01 (Key)],

     Date(Date#([Net due date - Net due date Level 01 (Text)], 'DD.MM.YYYY'), 'DD-MM-YYYY') as [Date],

     [Company code - Company code Level 01 (Key)],

     [Company code - Company code Level 01 (Text)] as [Company Code],

     [Vendor - Vendor Level 01 (Key)],

     [Vendor - Vendor Level 01 (Text)] as [Vendor],

     [Document Date - Document Date Level 01 (Key)],

     [Document Date - Document Date Level 01 (Text)] as [Document Date],

     [Document type - Document type Level 01 (Key)],

     [Document type - Document type Level 01 (Text)] as [Document Type],

     [Item - Item Level 01 (Key)],

     [Item - Item Level 01 (Text)],

     [Document no. - Document no. Level 01 (Key)],

     [Document no. - Document no. Level 01 (Text)] as [Document ID],

     [Item Text - Item Text Level 01 (Key)],

     [Item Text - Item Text Level 01 (Text)],

     [Posting date - Posting date Level 01 (Key)],

     Date(Date#([Posting date - Posting date Level 01 (Text)], 'DD.MM.YYYY'), 'DD-MM-YYYY') as [PostingDate],

     [Fiscal year/period - Fiscal year/period Level 01 (Key)],

     [Fiscal year/period - Fiscal year/period Level 01 (Text)] as [Fiscal Period],

     [Document currency - Document currency Level 01 (Key)],

     [Document currency - Document currency Level 01 (Text)] as [Document Currency],

     [Local currency - Local currency Level 01 (Key)],

     [Local currency - Local currency Level 01 (Text)],

     Amount,

     [Amount - Unit],

     [Credit amt in LC],

     [Credit amt in LC - Unit],

     [Debit amt in LC],

     [Debit amt in LC - Unit],

     [Debit/Credit Amount],

     [Debit/Credit Amount - Unit],

     [Discount base],