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: 
Not applicable

Associate NVARCHAR(1) with Excel file

Hi, I have na sql table where SEX is store as a nvarchar(1), being 0 = male and 1 = female.

To have better understanding on the graphics, I created na auxiliary Excel table with a column translating the values to description. Somethign like this:

sexsex_desc
0Male
1Female

When I am profiling the file, it says that despite having the same name, he can't associate the column sex from my auxiliary table with sex from the original table because they contain diferente values! He suggests I associate with other columns from the original table, whose datatype is BIT instead of NVARCHAR(1).

I think it doesn't associate with the column I want because of the data type, but both have 0s and 1s ... how can i solve this? Save the excel with diferente format?

Best regards,

Carlos Silva

1 Solution

Accepted Solutions
reddy-s
Master II
Master II

Hi Carlos,

I am assuming that your fact table has a column "Sex". So while importing the data just use this : num(sex) as Sex - > this will change the format to a number. Do the same with the Dimension table as mentioned above:

[Sex Classification]:

load

       num(sex) as Sex,

       Sex_desc from [LIB://.....];

This should form an association between the fact table and the [Sex Classification] dimension table on the Sex field.

Thanks,

Sangram Reddy.

View solution in original post

5 Replies
reddy-s
Master II
Master II

Hi Carlos,

I am assuming that your fact table has a column "Sex". So while importing the data just use this : num(sex) as Sex - > this will change the format to a number. Do the same with the Dimension table as mentioned above:

[Sex Classification]:

load

       num(sex) as Sex,

       Sex_desc from [LIB://.....];

This should form an association between the fact table and the [Sex Classification] dimension table on the Sex field.

Thanks,

Sangram Reddy.

Not applicable
Author

I'm testing this in the Data Load Editor, and have created a section where I import the auxiliary table [Sex_Classification], but, to avoid unlocking the autogenerated section, there is any way to override the load of the Dimension Table?

Best Regards,

Carlos

reddy-s
Master II
Master II

Hi Carlos,

You need to unlock the auto-generated section to work on it. Unlocking will not effect the script.

Thanks,

Sangram,

Not applicable
Author

Doing the "cast" on both tables worked perfectly! Thanks!

Best regards,

Carlos Silva

reddy-s
Master II
Master II

Glad it Worked!