topic Re: How to create a date field from another date field in QlikView

How to create a date field from another date field

akpofureenughwu — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to create a date field from another date field

m_woolf — Wed, 04 May 2016 16:13:24 GMT

In script:

date(PostingDate+45) as NewDate

Re: How to create a date field from another date field

akpofureenughwu — Wed, 04 May 2016 16:25:14 GMT

Hello sir,It didn't work.

Re: How to create a date field from another date field

akpofureenughwu — Wed, 04 May 2016 16:26:59 GMT

Please find the script in txt file for easier understanding.

Re: How to create a date field from another date field

sunny_talwar — Wed, 04 May 2016 16:34:23 GMT

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;

Re: How to create a date field from another date field

sunny_talwar — Wed, 04 May 2016 16:57:33 GMT

Did you look at what I proposed?

Re: How to create a date field from another date field

akpofureenughwu — Wed, 04 May 2016 17:14:34 GMT

@ 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

Re: How to create a date field from another date field

sunny_talwar — Wed, 04 May 2016 17:26:04 GMT

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]

Re: How to create a date field from another date field

akpofureenughwu — Wed, 04 May 2016 17:40:09 GMT

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

Re: How to create a date field from another date field

sunny_talwar — Wed, 04 May 2016 17:48:14 GMT

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],

Re: How to create a date field from another date field

akpofureenughwu — Wed, 04 May 2016 17:57:36 GMT

Thanks Man... The Due Date field is empty. Please can u review the script so we can try it again?

Thanks

Re: How to create a date field from another date field

sunny_talwar — Wed, 04 May 2016 18:06:31 GMT

Did you make all these changed my friend?

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],

Re: How to create a date field from another date field

akpofureenughwu — Wed, 04 May 2016 18:22:48 GMT

Hello Sunny, It gave me an error ... Date field not found,.....

Can we try again? Thanks

Re: How to create a date field from another date field

sunny_talwar — Wed, 04 May 2016 19:39:14 GMT

Did you remove this from your script?

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

Re: How to create a date field from another date field

akpofureenughwu — Thu, 05 May 2016 09:41:24 GMT

Hello sunny, I didn't change the script.

Still not give me the due date field parameters.

Re: How to create a date field from another date field

sunny_talwar — Thu, 05 May 2016 14:29:05 GMT

Would you be able to share the log file?

Re: How to create a date field from another date field

akpofureenughwu — Mon, 09 May 2016 13:18:33 GMT

I don't understand Mr Sunny. This issue is still on my desk.. Thanks so far

Re: How to create a date field from another date field

sunny_talwar — Mon, 09 May 2016 15:15:37 GMT

Check here to learn how to generate a log file

How to generate Log files in QV

Re: How to create a date field from another date field

akpofureenughwu — Mon, 09 May 2016 15:23:03 GMT

I have followed the instructions on that link (generate log file). I was told that the log file will be in the same folder/ocation where the qvw file is but I didn't see anything there. More clues please

Re: How to create a date field from another date field

sunny_talwar — Mon, 09 May 2016 16:49:37 GMT

Did you reload the application after you enabled the log file?