topic Re: SQL 2008 import - IF statement loading data into two lines instead of one in QlikView

SQL 2008 import - IF statement loading data into two lines instead of one

darren_dixon — Wed, 21 Nov 2012 14:26:36 GMT

When importing from sql my data is appearing on 2 lines instead of one.

I think this is because I'm trying to link the information by IF Statements. Is there a better way to load this?

My script is:

My SQL2008 Table looks like this.

Can you please help.

Thanks,

Darren

Re: SQL 2008 import - IF statement loading data into two lines instead of one

flipside — Wed, 21 Nov 2012 15:09:18 GMT

Hi Darren,

What your script is doing is reading the 11 lines in your SQL Table and then creating 1 line when OptitimeResourceID matches and another for when Operative Number matches. I'd guess the third line is the other 8 lines rolled up.

If you load your data into a QV table first, you can then create your desired table as follows ...

FirstTouchProfiles:

LOAD

UserName as "SHGUserName",

Value as "OptitimeResourceID"

Resident Data

where PropertyName='OptiTime_ResourceID';

left join (FirstTouchProfiles)

LOAD

UserName as "SHGUserName",

Value as "Operative Number"

Resident Data

where PropertyName='Operative Number';

DROP TABLE Data;

flipside

Re: SQL 2008 import - IF statement loading data into two lines instead of one

darren_dixon — Wed, 21 Nov 2012 15:39:58 GMT

Hi Flipside,

Thanks for your response.

Basic question but could you point me in the direction loading my data into a QV table.

My experience of Qlikview has only led me to import directly from SQL tables.

Thanks,

Darren

Re: SQL 2008 import - IF statement loading data into two lines instead of one

flipside — Wed, 21 Nov 2012 15:47:18 GMT

Yes, no problem.

In the QV load script, first load data as follows into a table called Data (or tmp or whatever you like):

Data:

SQL SELECT {your command}

... then use the code I posted earlier. The word Resident references a previously loaded QV table in the script, and you can drop the table after because you won't need it again.

If you only need the two fields, then you could filter your sql to where PropertyName = etc, etc, but if only a few rows in total it probably won't be worth it.

flipside

Re: SQL 2008 import - IF statement loading data into two lines instead of one

darren_dixon — Wed, 21 Nov 2012 16:47:34 GMT

This worked perfectly.

Thank you very much for your help. Greatly appriciated.

Darren