topic Re: Covert Date Format from MM/YYYY TO MMM-YYYY in QlikView

Covert Date Format from MM/YYYY TO MMM-YYYY

arpita — Fri, 11 Dec 2020 13:26:21 GMT

I created my date field from the file name extension which is in MM/YYYY format but I need it in MMM-YYYY format.

File Name : TTA Reports202011010404288823

FXVOls:
LOAD
Status,
Product,
[Last Amend Date],
[Trading_Entity],
mid(FileName(),46,2)&'/'&mid(FileName(),42,4) as Year _Month

FROM
$(file)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

The output of Year_Month is 11/2020 but I want it as Nov-2020.

2nd question: Is there a possibility that I can have my QV app reflecting Oct-2020 instead of Nov-2020 from the same file string TTA Reports202011010404288823

Re: Covert Date Format from MM/YYYY TO MMM-YYYY

dplr-rn — Fri, 11 Dec 2020 14:14:21 GMT

Change to something like below

Date(Date#(mid(FileName(),46,2)&'/'&mid(FileName(),42,4),'MM/YYYY'),'MMM-YYYY') as Year _Month

regarding your second question. use Add months function around the date# expression.

Date(AddMonths(date#(...),-1),'MMM-YYYY')

Re: Covert Date Format from MM/YYYY TO MMM-YYYY

arpita — Mon, 14 Dec 2020 14:51:39 GMT

Thanks dilipranjith,

This part did work: Date(Date#(mid(FileName(),46,2)&'/'&mid(FileName(),42,4),'MM/YYYY'),'MMM-YYYY') as Year _Month

FXVOls:
LOAD
Status,
Product,
[Last Amend Date],
[Trading_Entity],
Date(Date#(mid(FileName(),46,2)&'/'&mid(FileName(),42,4),'MM/YYYY'),'MMM-YYYY') as Year _Month,
Date(AddMonths(date#(Year_Month),-1),'MMM-YYYY')

FROM
$(file)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

But when I add the 2nd line to the script Date(AddMonths(date#(Year_Month),-1),'MMM-YYYY')....this doesnt work and says cannot find Field Year_Month

Please help