topic How can i combine a Text and Date field in a Join statement in QlikView

How can i combine a Text and Date field in a Join statement

tbeenders — Fri, 03 Oct 2025 09:56:55 GMT

I'm trying to compare a combined string and date field in a join statement. But it gives me a syntax error. What am i doing wrong?

InterPolisnummer = Text, Ingangsdatum = Date

LEFT OUTER JOIN PUB.DekkingLeeftijd dklt
ON DKL.InternPolisnummer + convert(varchar, DKL.Ingangsdatum, 101) = DKLT.InternPolisnummer + convert(varchar, DKLT.Ingangsdatum, 101)

Re: How can i combine a Text and Date field in a Join statement

robert_mika — Fri, 03 Oct 2025 10:03:41 GMT

In sql that would probably work with concat()

In qlik scrippting with &

Re: How can i combine a Text and Date field in a Join statement

tbeenders — Fri, 03 Oct 2025 10:13:45 GMT

In SQL that will be like?

I have tried 2 options. First one give me another syntax error, 2nd one a Inconsistent type error.

ON concat(DKL.InternPolisnummer, convert(varchar, DKL.Ingangsdatum, 101)) = concat(DKLT.InternPolisnummer, convert(varchar, DKLT.Ingangsdatum, 101))

Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "varchar, DKL.Ingangsdatum, 101)) = conca" (10713)

ON concat(DKL.InternPolisnummer, DKL.Ingangsdatum) = concat(DKLT.InternPolisnummer, DKLT.Ingangsdatum)

Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 4294947288, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Inconsistent types (7481)

Re: How can i combine a Text and Date field in a Join statement

tbeenders — Fri, 03 Oct 2025 10:33:38 GMT

I found something on CoPilot:

ON CONCAT(DKL.InternPolisnummer, TO_CHAR(DKL.Ingangsdatum, 'MM/DD/YYYY')) = CONCAT(DKLT.InternPolisnummer, TO_CHAR(DKLT.Ingangsdatum, 'MM/DD/YYYY'))

Thx for your input!

Re: How can i combine a Text and Date field in a Join statement

SunilChauhan — Fri, 03 Oct 2025 10:41:25 GMT

try with two conditon with and
ON DKL.InternPolisnummer = DKLT.InternPolisnummer and
convert(varchar, DKL.Ingangsdatum, 101)= convert(varchar, DKLT.Ingangsdatum, 101)