Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rsvebeck
Valued Contributor

Load Script with flexible fields

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

1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: Load Script with flexible fields

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) ;

7 Replies
jitiong90
New Contributor III

Re: Load Script with flexible fields

Hi,

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

like

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


Regards,

Tiong


MVP
MVP

Re: Load Script with flexible fields

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

MVP
MVP

Re: Load Script with flexible fields

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
flipside
Valued Contributor II

Re: Load Script with flexible fields

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) ;

rsvebeck
Valued Contributor

Re: Load Script with flexible fields

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

rsvebeck
Valued Contributor

Re: Load Script with flexible fields

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

rsvebeck
Valued Contributor

Re: Load Script with flexible fields

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

Community Browser