How to prevent conversion of string to date

stascher — Fri, 26 May 2023 21:44:57 GMT

In my database, I have a SQL table containing varchar Employee IDs that Qlik is attempting to convert to dates and I don't know why. For example:

EmployeeID	Qlik is auto-converting to:
710-02-027	710-02-027 as a date/dual value
710-02-028	710-02-028 as a date/dual value
710-02-029	710-03-001 as a "corrected" date/dual value
710-02-030	710-03-002 as a "corrected" date/dual value
710-02-031	710-03-003 as a "corrected" date/dual value
710-02-032	710-02-032 as a string value
710-02-033	710-02-033 as a string value
710-02-034	710-02-034 as a string value

As you can imagine, this is causing havoc to my data model.

Within my app...

the DateFormat setting is: SET DateFormat='M/D/YYYY';

the load script simply queries this field using: Select EmployeeID as "Employee ID";

I've tried casting EmployeeID as varchar in the above script and it doesn't make an difference.

Can someone please tell me how to stop Qlik from auto-converting these values into dates and only load them as the strings they are stored as?

Thank you very much.

Steve

Re: How to prevent conversion of string to date

BrunPierre — Sat, 27 May 2023 17:40:46 GMT

Hi @stascher , Text() will force interpretation as text.

Text(EmployeeID) as EmployeeID

stascher — Mon, 29 May 2023 13:13:06 GMT

Thanks. I actually found the solution on my own but since you were nice enough to answer I'll give you credit for it.

-Steve