Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
imark
Contributor III
Contributor III

Any workaround for CrossTable hard string

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?

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

1 Reply
sunny_talwar

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;