Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Alam
Contributor
Contributor

String and Date formats in the same column

Using QlikSense

My raw data contains 2 columns, Type and Date

The Date column contains  the same data type values but in two different forms; like '2023-06-20' as well as some cells containing 'Incorrect details'.

When I load the data into Qlik, I wrote the following to create a new calculated field 'FormattedDate' as follows:

If(IsNum(Date([Date],'dd-MM-yyyy')),Date([Date],'dd-MM-yyyy'),[Date])

This allows me to format the Date column in my visualization so that the error message 'Incorrect details' is also displayed where it occurs while all other values are formatted as specified.

Now, in another table, I am wanting a summary table that shows each value for 'Type' column and the corresponding LATEST Date for that control. Usually, a Max(Date) function would solve it but I want it to work such that if a Type has x number of dates against it, and one of them can't be formatted as a date i.e. 'Incorrect details' value, then the Latest date for that Type should show the value 'Incorrect details' otherwise if a Type has values against it all of which can be calcualated as Max(Date), then let it calculate Max(Date) as normal.
I have tried:

If(IsNum(Max({<FormattedDate={'*'}>}Num(FormattedDate,'dd-MM-yyyy'))),
Max({<FormattedDate={'*'}>}Num(FormattedDate,'dd-MM-yyyy')),
Max({<FormattedDate={'*'}>}FormattedDate)) 

but this returns a blank table altogether.

Any help will be greatly appreciated.

Labels (4)
1 Reply
marcus_sommer

I would use a bit different approach and avoiding all formatting stuff and querying the content with if-loops. Probably with something like:

floor(Date) as DateKey, // will be connected to a calendar
pick(sign(alt(Date, 0)) + 1, dual('invalid', 0), dual('valid', 1)) as DateFlag

and afterwards using the DateFlag as set analysis condition or just as multiplicator or maybe as selection.