Associate NVARCHAR(1) with Excel file

[#1]

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:

sex	sex_desc
0	Male
1	Female

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

[reddy-s · Accepted solution]

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.

[#3]

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]

Hi Carlos,

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

Thanks,

Sangram,

[#5]

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

Best regards,

Carlos Silva

[reddy-s]

Glad it Worked!