topic Re: Converting Data Format in QlikView

Converting Data Format

brunolelli87 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Converting Data Format

Lisa_P — Tue, 28 Apr 2020 22:45:28 GMT

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/InterpretationFunctions/date_hash.htm

For date formatting:

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

Re: Converting Data Format

Taoufiq_Zarra — Tue, 28 Apr 2020 22:46:45 GMT

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')) =>

Re: Converting Data Format

brunolelli87 — Tue, 28 Apr 2020 23:11:23 GMT

Thanks,

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

Thank you so much

Re: Converting Data Format

Taoufiq_Zarra — Tue, 28 Apr 2020 23:18:03 GMT

the correct formula :

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

why .?

for example :

MMM->Apr

MMMM->April

MM->04

Re: Converting Data Format

brunolelli87 — Tue, 28 Apr 2020 23:36:04 GMT

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

Re: Converting Data Format

Taoufiq_Zarra — Wed, 29 Apr 2020 00:14:26 GMT

for this you'll need:

IntervalMatch

and

Creating A Master Calendar