Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can change the format and then create a different format column
load
date, date(date,'DD/MM/YYYY') AS Date1
and so on..
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.
Hi SHIVESH,
But i want different row formats for different rows for single column
loveisfail stalwar1pls help
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
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.
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.
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.
Hi Colin,
How is this achieved?? I have US and UK clients and the date formats are a sticking point for sure.
Rayna