topic Re: different date formats in QlikView

different date formats

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

Hi all,

Can we have a same column with different date formats in a single table?

Ps.this question was asked in interview

Regards

Manu

Re: different date formats

shiveshsingh — Sun, 18 Feb 2018 15:51:13 GMT

You can change the format and then create a different format column

load

date, date(date,'DD/MM/YYYY') AS Date1

and so on..

Re: different date formats

markp201 — Sun, 18 Feb 2018 16:24:32 GMT

A date is internally just a number so you can format whichever way you like?

Each time you format it will be applied to all rows.

You can add specific formats based on conditions.

If instead you have a date column in different formats you need to load you will have to

use DATE#(field,format) which expects the date in the defined format. I believe if the

field is not in the prescribed format, it will return null (not sure - a simple test can be done).

Often DATE and DATE# are used to standardize date fields or convert them from one

format to another.

Re: different date formats

manu1512 — Sun, 18 Feb 2018 17:25:03 GMT

Hi SHIVESH,

But i want different row formats for different rows for single column

Re: different date formats

manu1512 — Sun, 18 Feb 2018 17:25:38 GMT

loveisfail‌ stalwar1‌pls help

Re: different date formats

ahmar811 — Mon, 19 Feb 2018 04:56:42 GMT

Hi Sharma,

I think you try Alt function it take different format of date like below and convert to one format

load Date(Alt(Date#(Date,'DD/MM/YYYY'),Date#(Date,'DD-MM-YYYY'),Date#(Date,'DD.MM.YYYY'),Date#(Date,'YYYY.MM.DD')),'DD-MM-YYYY') as Date2;

LOAD * INLINE [

Date

12/03/2018

23-05-2018

26.12.2017

2018.02.13

];

Regards

Ahmar

Re: different date formats

Anil_Babu_Samineni — Mon, 19 Feb 2018 13:04:10 GMT

Until unless, If you won't sufficient question i believe you won't get any output. Will you provide some input data and explain result data as always.

Re: different date formats

Miguel_Angel_Baeyens — Mon, 19 Feb 2018 15:24:17 GMT

Short answer: yes, "dates" for QlikView are values just by other name, i.e.: QlikView does not have data types.

As Ahmar points above, it all comes to how you transform that data during the LOAD process which happens in the script. Alt() is one option, I would suggest If() as well.

If the question is not about the datamodel but in a straight or pivot chart, it is also possible using likewise Alt() or If(), not recommended for large sets of data but it will work.

If the question is about a Table box object then the answer is also yes, you can display whatever value you load from the source but you will not be able to format them the same evenly, each will display with the format they come from the data source.

Re: different date formats

Colin-Albert — Mon, 19 Feb 2018 17:43:33 GMT

You can configure the date format strings so the date format is based on the user's preferences e.g DD/MM/YYYY for UK users, MM/DD/YYYY for US users and DD.MM.YYYY for european users, and then apply that setting in the chart.

So the same table will format the date differently depending on which use is connected to the application.

Re: different date formats

raynac — Thu, 03 May 2018 15:25:43 GMT

Hi Colin,

How is this achieved?? I have US and UK clients and the date formats are a sticking point for sure.

Rayna

Re: different date formats

Colin-Albert — Tue, 08 May 2018 15:41:01 GMT

Have a look at this post from marcus_sommer

Re: handling timezones and formats for date/time,number and money

Re: different date formats

varaprasad0816 — Fri, 28 Feb 2020 09:00:02 GMT

Awesome