Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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) ;
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
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
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.
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) ;
Thanks Tiong, but this did not help me this time. /Robert
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
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