Trouble with date formatting

[tetew89]

Hi everyone,

I have a table with a column called RelativeMonth and it has two types of values, one is the normal month and one is the MAT. For the normal month the format is 'YYY  MMM' (e.g. 2023 Apr). For the Mat it is 'Mat MMM YYY' (e.g. Mat Apr 2023).

In my script I am creating a new column called Period_Type that specifies whether the date is MAT or Month. So I want to have the values in the RelativeMonth column in the same format. That is why I am trying to transform the Mat values such as Mat Apr 2023 into the same format as the Month values (so 2023 Apr). The script I have come up with is this one:

tt:

LOAD
if(wildmatch(RelativeMonth, '*Mat*'), 'MAT', 'MONTH') as "PERIOD_TYPE",
If(wildmatch(RelativeMonth, '*Mat*'), date#(right(trim(RelativeMonth), 8)), RelativeMonth) as RelativeMonth,

from [lib://Monthly/monthly_new.qvd] (qvd);

Tp:
NoConcatenate load PERIOD_TYPE,
date(alt(num(date#(RelativeMonth, 'MMM YYYY')), num(date#(RelativeMonth, 'YYYY MMM'))), 'YYYY MMM') as RelativeMonth
resident tt;
drop table tt;

As you can see I had to create two separate tables so I can first get rid of the Mat part in front of the real date, and then in the second table I am trying to transform it into the same format as the monthly values. However, the date format I get is the following: 2022 abr. 

So the first letter is not capitalized, they use the Spanish writing (abr instead of apr) and a dot is added at the end. 

Does anyone know how to solve this? Also, do you think it is possible to put the transformation into just one single load statement, instead of having to cut out Mat in one load script and then do the reformatting in another?

Thank you so much!

[rwunderlich]

I think you should be able to transform the data in a single load using

Date(
Alt(Date#(RelativeMonth, 'YYYY MMM'), Date#(Right(RelativeMonth,8), 'MMM YYYY '))
,''YYYY MMM') as RelativeMonth

-Rob

[BrunPierre]

Do this to load once:

Date(Alt(Date#(RelativeMonth, 'YYYY MMM'), Date#(Right(RelativeMonth,8), 'MMM YYYY')),'YYYY MMM') as RelativeMonth

The '2022 abr.' couldn't have resulted from the transformation but represents a distinct value type. Is this an isolated instance, or are there more occurrences of such values within the dataset?

[joshsiddle8]

Hi there,

It seems like you're encountering some formatting issues with your date transformation script. To address the lowercase and Spanish month abbreviation concerns, you can try using the Upper() function to capitalize the first letter and the 'MMM' date format string to ensure English month abbreviations. Additionally, you can streamline the transformation process by combining the logic into a single load statement using nested if conditions within the Load script.

Here's a revised version of your script with these adjustments:

css
Copy code
tt:
LOAD
if(wildmatch(RelativeMonth, '*Mat*'), 'MAT', 'MONTH') as "PERIOD_TYPE",
If(wildmatch(RelativeMonth, '*Mat*'), date#(right(trim(RelativeMonth), 8)), RelativeMonth) as RelativeMonth
FROM [lib://Monthly/monthly_new.qvd] (qvd);

Tp:
NoConcatenate
LOAD
PERIOD_TYPE,
if(PERIOD_TYPE = 'MAT', Date(Upper(Mid(RelativeMonth, 5, 3)) & ' ' & Right(RelativeMonth, 4), 'MMM YYYY'), Date(RelativeMonth, 'YYYY MMM')) as RelativeMonth
RESIDENT tt;

DROP TABLE tt;
This should help address the formatting discrepancies and streamline the transformation process. Let me know if you need further assistance!

Best regards,