topic Re: Load Script with flexible fields in QlikView

Load Script with flexible fields

RSvebeck — Fri, 28 Sep 2012 08:22:22 GMT

I want to create a Qlikview document that should be able to load data from two different datasources, depending on a paramenter.

My script starts like this:

SET ProdTest = 'T';

if $(ProdTest) = 'T' then

SET Server = '\\w2k3qw2\';

else

SET Server = '\\w2k8qw4\';

end if

So I can easily change the root path from a "test" environment to a "production" envirionment.

Now, later in the script, when loading the actual data I use this method:

items:

load

itemno,

itemname

FROM $(Server)\Qvd\items.qvd (qvd);

This works great.

Now to my dilemma:

The QVD in the two paths are not completely the same, the test QVD will contain columns that the prod QVD does not have.

So how can I write a script that can handle this?

I have tried:

items:

load

itemno,

itemname,

if('$(ProdTest)'='T',NewTestField,'') as NewTestField

FROM $(Server)\Qvd\items.qvd (qvd);

But it seems to not work. I dont get any error, but the field NewTestField is always blank, both for test and prod QVD.So it seems my if(...) does not work.

Any help appreciated.

Best Regards

Robert

Re: Load Script with flexible fields

Anonymous — Fri, 28 Sep 2012 08:38:17 GMT

Hi,

Remove the single quotations that wrap the '$(ProdText)' in your if statement, change it from '$(ProdText)' to $(ProdText),

if($(ProdTest)='T',NewTestField,'') as NewTestField

Regards,

Tiong

Re: Load Script with flexible fields

swuehl — Fri, 28 Sep 2012 08:44:25 GMT

I think you need to use the If..then..elseif..else..end if script statements to control the flow in your script.

I don't think you can use these within the load, so you need to create two loads, and control which load to use using

If..then..elseif..else..end if

Hope this helps,

Stefan

Re: Load Script with flexible fields

jonathandienst — Fri, 28 Sep 2012 08:47:12 GMT

Robert

You cant do that I am afraid, the present of NewTestField will trigger an error in the load even if you are not using it. You can split this into two load expressions like this:]

If ProdTest = 'T' Then
     items:
     load  itemno,
       itemname,
       NewTestField
     FROM $(Server)\Qvd\items.qvd (qvd);
Else
     items:
     load itemno,
       itemname,
       null() As NewTestField
     FROM $(Server)\Qvd\items.qvd (qvd);
End If

The null() is to ensure that the two load statements auto concatenate.

Hope that helps

Jonathan

PS you could alos use LOAD * FROM .... to load all the fields, but you may have concatenation issues.

Re: Load Script with flexible fields

flipside — Fri, 28 Sep 2012 09:28:11 GMT

Try loading the script into a variable similar to this example ...

OriginalData:
LOAD * INLINE [
itemno, itemname, TestField
A, Apples, Cooking Apples
B, Bananas, Small Bananas
C, Carrots, Baby Carrots
D, Duck, Duck a l'Orange
E, Eggs, Easter Eggs];

LET ProdTest = ', TestField as TestField_new';
//LET ProdTest = '';

LET vLoadScript =
'itemno as itemno_new,
itemname as itemname_new' &
'$(ProdTest)' &
' resident OriginalData'; // note preceding space

items:
LOAD $(vLoadScript) ;

Re: Load Script with flexible fields

RSvebeck — Wed, 10 Oct 2012 08:42:24 GMT

Thanks Tiong, but this did not help me this time. /Robert

Re: Load Script with flexible fields

RSvebeck — Wed, 10 Oct 2012 08:43:42 GMT

Thanks Stefan, I was hoping to not have to do this since I have so many tables and I wanted to find a simple elegant solution... //Robert

Re: Load Script with flexible fields

RSvebeck — Wed, 10 Oct 2012 08:47:50 GMT

This is the best solution. But I was hoping for a simple elegant solution and there seem to be none. All those solutions results in a script that gets complicated to read when I have a document that has 10 tables and all of them need to be able to be read from two different sources. Thanks for all help though! Appreciate it!

//Robert