Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
RSvebeck
Specialist
Specialist

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

Svebeck Consulting AB
1 Solution

Accepted Solutions
flipside
Partner - Specialist II
Partner - Specialist II

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

View solution in original post

7 Replies
Anonymous
Not applicable

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


swuehl
MVP
MVP

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
Partner - Specialist II
Partner - Specialist II

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
Specialist
Specialist
Author

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

Svebeck Consulting AB
RSvebeck
Specialist
Specialist
Author

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

Svebeck Consulting AB
RSvebeck
Specialist
Specialist
Author

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

Svebeck Consulting AB