topic Re: Convert string to date in Qlik Automate

Convert string to date

FelipeDenani — Tue, 11 Mar 2025 15:27:22 GMT

Hi, can you help me please?

I have the string field "endDate",

That contains values as "01/02/2025".

I need to convert this string to date, how can I do that?

Re: Convert string to date

henrikalmen — Tue, 11 Mar 2025 16:00:32 GMT

Date(Date#(endDate, 'MM/DD/YYYY'), 'MM/DD/YYYY')

Re: Convert string to date

FelipeDenani — Tue, 11 Mar 2025 17:17:36 GMT

I think not worked,

I need something like this:

Re: Convert string to date

marksouzacosta — Tue, 11 Mar 2025 19:52:29 GMT

Hi @FelipeDenani,

Are you from Brasil?

Try the code below in your Load Script. I'm assuming your code is in the MM/DD/YYYY format:

MyRecords:
LOAD
endDate,
Date(MonthStart(endDate),'YYYY-MMM') AS endDateFormatted
;
Load
Date#(endDate,'MM/DD/YYYY') AS endDate
Inline [
endDate
01/02/2025
02/08/2025
03/12/2025
04/22/2025
];

This is the result:

Regards,

Mark Costa

Re: Convert string to date

FelipeDenani — Tue, 11 Mar 2025 19:57:03 GMT

Hi Mark, yes I'm from Brazil.

Not worked, I'm using Qlik in Sailpoint interface.

Re: Convert string to date

marksouzacosta — Tue, 11 Mar 2025 20:33:26 GMT

Legal, também sou! Vou responder o tópico em inglês pra não ficar muito estranho rs.

// ***************

Ok so, you have at least two options:
a) Create a Calculated Dimension - like you are doing
b) Add an extra field or change in your Load Script the existing endDate field (recommended)

Option A: Calculated Dimension

Use this expression instead of the one that I sent to you:

=Date(MonthStart(Date#(endDate,'MM/DD/YYYY')),'YYYY-MMM')

Option B: Change the Load Script (recommended)
It is recommended to avoid Calculated Dimensions for multiple reasons. So, when possible, is recommended to change your application Load Script.
Use my code as reference to modify your Load Script - my code is not the final solution, but a reference. You can add a new field - like in my example where I have added endDateFormatted field - or you can change the expression that calculates your endDate field.

Please let me know if that helps.

Regards,

Mark Costa

Re: Convert string to date

FelipeDenani — Tue, 11 Mar 2025 20:37:54 GMT

Opa aí sim, muito prazer!

Obrigado, vou tentar aqui. Amanhã retorno com updates.

Thank you!