Any workaround for CrossTable hard string

imark — Sat, 16 Nov 2024 18:38:35 GMT

From what I just experienced, it seems that the column headers that are being pivoted whilst calling CrossTable are cast to a "hard" string. What I mean by hard here is that the common duality of integer and string appears to be suspended. In the code below, this would be the case for Month (which looks like this: 202005)

FxGbp: CrossTable(Month, FxRate, 3) LOAD * FROM [$(vDirPath)\ManualInputs.xlsx] (ooxml, embedded labels, table is FxGbp)

I made some tests and things along the line of

NUM(Month, '#') or Month * 1

fail. On the other hand

Month & '_' works and returns 202005_

I have to join this data further down the script with another field that under normal conditions is treated as an integer. It comes from SQL by the way, but it keeps being treated as integer also if I explicitly cast it as NVARCHAR, which I do either way

LEFT(CAST(TxDateSerial AS NVARCHAR), 6) AS month_serial

20200514 -> 202005

The only way I managed to have the join work thus far is by "forcing" the SQL field into a string by concatenating a '_' after the LEFT(), but I really don't like this kind of bodges.

Is there a way to have the CrossTable month treated as an integer/dual?

Re: Any workaround for CrossTable hard string

sunny_talwar — Thu, 14 May 2020 12:48:11 GMT

You can take a resident load of this to fix your field... for example

FxGbp: CrossTable(Month, FxRate, 3) LOAD * FROM [$(vDirPath)\ManualInputs.xlsx] (ooxml, embedded labels, table is FxGbp); FinalFxGbp: LOAD *, Num(Num#(Month)) as Month1, Date(Date#(Month, 'YYYYMM'), 'YYYYMM') as Month2 Resident FxGbp;

topic Re: Any workaround for CrossTable hard string in QlikView

Any workaround for CrossTable hard string

Re: Any workaround for CrossTable hard string