Converting Data Format

[brunolelli87]

Dears,

I have a database with a Dates column, named DATE_STOCK, but the dates are formatted as string, for example: "01 de abr de 2020"; so it's not well-formatted as a date.

How can I convert it to 01/04/2020 (DD/MM/YYYY)? 

Check some more examples:

04 de fev de 2019 > 04/02/2019
05 de dez de 2020 > 05/12/2020

Thanks for your help
Bruno Lelli

[Lisa_P]

Use the Date#() function to tell the format of the data being loaded. Take a look at help:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Inter...

For date formatting:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/date-...

[Taoufiq_Zarra · Accepted solution]

if you're worried that 'de ' will be interpreted differently you can use

=Date(Date#(replace(YOURFIELD,' de ','/'),'DD/MMMM/YYYY'))

example :

=Date(Date#(replace('04 de févr. de 2020',' de ','/'),'DD/MMMM/YYYY')) =>

[brunolelli87]

Thanks, 

But I'm just wondering why MMMM instead of just MM?

Thank you so much

[Taoufiq_Zarra]

the correct formula :

=Date(Date#(replace(YOURFIELD,' de ','/'),'DD/MMM/YYYY'))

why .?

for example :

MMM->Apr

MMMM->April

MM->04

[brunolelli87]

Nice, I got it!

Thanks a lot

Another question...

I have some data on spreadsheets classified by quarters, like 1Q2019, 2Q2019, 3Q2019... and the column name is Quarter.

In another spreadsheet I have some information classified by dates, like 01/02/2019, 01/10/2019, 04/09/2019... and the column's name is Days

So, how can I connect everything? I mean, if the user selects one date the system should automatically select the corresponding quarter and vice verse. 

Example:

Selecting: 03/05/2019 (DD/MM/YYYY)
The system should automatically select 02Q2019

Selecting 22/11/2019 (DD/MM/YYYY)
The system should automatically select 4Q2019

How can I do it?
Thanks again

[Taoufiq_Zarra]

for this you'll need:

• IntervalMatch

and

• Creating A Master Calendar