Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Wondering about Qlik Talend Data Integration Sessions? There are 11, in addition to all of the Data & Analytics. So meet us in Orlando, June 3 -5.
Browse our helpful how-to's to learn more about navigating Qlik Community and updating your profile.
Join us on April 24th at 10 AM ET for the next Do More with Qlik webinar focusing on Qlik’s Data Integration & Quality solutions.
Your journey awaits! Join us by Logging in and let the adventure begin.
Qlik enables a frictionless migration to AWS cloud by Empresas SB, a group of Chilean health and beauty retail companies employing 10,000 people with 600 points of sale.
Qlik Luminary Stephanie Robinson of JBS USA, the US arm of the global food company employing 70,000 in the US, and over 270,000 people worldwide.
Join one of our Location and Language groups. Find one that suits you today!
A private group is for healthcare organizations, partners, and Qlik healthcare staff to collaborate and share insights..
Qlik Communityの日本語のグループです。 Qlik製品に関する日本語資料のダウンロードや質問を日本語で投稿することができます。
Welcome to the group for Brazil users. .All discussions will be in Portuguese.
Hear from your Community team as they tell you about updates to the Qlik Community Platform and more!