topic Re: Error Message with Sql Server 2008 R2 in QlikView

Error Message with Sql Server 2008 R2

Thu, 26 Jul 2012 22:40:59 GMT

Grettings, I have a problem with the DB, I was try it load data from my DB to a QVD files, and the error is at the moment to try load null values on my table's columns. Not all the data is null, but is a sql left join, then some data is null.

This is the sql sentence that I try it load:

RecruitTermination:

SQL SELECT FactEmployee.GlobalEmployeeId,

DimRecruitmentType.RecruitmentTypeId,

DimRecruitmentType.OriginalCode,

DimRecruitmentType.Description,

DimTerminationReason.TerminationReasonId,

DimTerminationReason.OriginalCode,

DimTerminationReason.Description,

DimTerminationReason.ShortDesc

FROM FactEmployee

INNER JOIN DimRecruitmentType

ON FactEmployee.RecruitmentTypeId = DimRecruitmentType.RecruitmentTypeId

LEFT JOIN DimTerminationReason

ON FactEmployee.TerminationReasonId = DimTerminationReason.TerminationReasonId;

STORE RecruitTermination

INTO C:\Users\Administrador\Desktop\Reporte Qlikview\RecruitTermination.qvd;

On the sql table, the first five columns have a full data, in other words, no null on this columns, but, the next columns we have a bug number of nulls.

So, in qlikview I was wrote before that expresion the sentence NULLASVALUE *; , but only works for the first column, DimTerminationReason.TerminationReasonId, for the second or anything else Qlikview show me a error message with the OLEDB conection ("Falló la lectura OLEDB", this is the error message, in spanish)

My question is, how I to get load all the columns with null values?, Thanks for the answer and I apologize for my poor level of English.

Re: Error Message with Sql Server 2008 R2

Anonymous — Thu, 26 Jul 2012 23:50:01 GMT

First step is to make sure that your SELECT statement works in SQL Server mangement studio. If there is a problem, it is easier to find it there. After you find and fix it, copy back to QV script.
Next, if it works there but doesn't work in QV script (never happened to me), try to load tables separeately, and join after that. It maybe not good for performance though:

RecruitTermination:
SQL SELECT
GlobalEmployeeId,
RecruitmentTypeId ,
TerminationReasonId
FROM FactEmployee;

T
INNER JOIN (RecruitTermination) LOAD *;
SQL SELECT
RecruitmentTypeId,
OriginalCode,
Description
FROM DimRecruitmentType ;

LEFT JOIN (RecruitTermination) LOAD *;
SQL SELECT
TerminationReasonId,
OriginalCode,
Description,
ShortDesc
FROM DimTerminationReason;

It is helpful to use log file, so you can see how many records loaded on each step.

Regards,
Michael
PS: maybe you need LEFT OUTER JOIN instead of INNER JOIN?

Re: Error Message with Sql Server 2008 R2

Fri, 27 Jul 2012 03:03:59 GMT

I suggest you do a RAW data extraction (meaning no JOINS at all). Store your raw data on each QVD under individual table names, then load your QVD into your data model and do all the joins that you want. That is a better ETL practice. NULLASVALUE doesn't seem to work on QV most of the time.

Regards,

Enard

Re: Error Message with Sql Server 2008 R2

Fri, 27 Jul 2012 15:35:00 GMT

Thanks you two for the answers.

Michael, obviously the SQL querys works on the managment studio, I was test it before wrote the Qlikview Script, a moment ago I notice the problem of Qlikview with my query, it's the null value of TerminationReasonId in the Join staments.

The first part, the inner join, it works successful, and at that case INNER JOIN and LEFT OUTER JOIN show the same result. The second part, in the left outer join, on Qlikview generates a error, because the Employees (GlobalEmployeeId), only a part of them, have a TerminationReasonId, then, the left outer join consult on Qlikview a null value of TerminationReasonId that compares a not null value of itself on the TerminationReasonId in the DimTermination table, Am I explaining it well?

Anyway, you suggest works in show the data that I want, but, my problem is other, which I thought that show it in that way resolve it, but thanks you.

Bernardine, I did the raw data extraction first of all, but on that way I not get the result that I want, for that reason I did try it with SQL JOIN.

I repeat, thanks you two so much, regards