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

[darren_dixon]

Hi

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

[flipside]

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

[darren_dixon]

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

[flipside · Accepted solution]

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

[darren_dixon]

This worked perfectly.

Thank you very much for your help. Greatly appriciated.

Darren