Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ericarru
Not applicable

Load table Where Exist ($(Variable that may be empty))

Hello,

I am working in a Foor loop where i have to chech for duplicates and I want to do it with an Exist. Comparing the Field of the New Table with the one of the Old Table. The point is that sometimes for some tables i have to do it for 1 field but sometimes for more.

My load is the Folowing

Table:

Load *

Resident MyResidenTable

where not( Exist ( $(Field1NewTable), $(Field1OldTable) ) ) 

and not( Exist ( $(Field2NewTable), $(Field2OldTable) ) ) ;


The problem appears that when $(Field2NewTable) and $(Field2OldTableare empty I can not do the load.

I Tryed Qlikview Loads the table if I do something like not( Exist ( 'RandomText', 'RandomText') ) . But if I use '$(Field1NewTable)'  it is text and when the Fields are not empty does not work Correctly.


Also I tryed to do if( len($(Field2NewTable))>0, $(Field2NewTable), 'RandomText') but QlikView is Still Complaining.


Is there some way to do that?


1 Solution

Accepted Solutions
Miguel_Angel_Baeyens
Not applicable

Re: Load table Where Exist ($(Variable that may be empty))

Ok, so it is not that the field is empty, is that the field does not even exist for the step of the loop you are in, therefore the variable is never populated, with neither 0 or anything else, because you are in step 3 while you only have 2 values, so in the step 3 of the loop, the field does not exist in that table (not that it is empty).

In this case you will need to do two loads: one loading everything you need to load, the second with the EXISTS() in the second load. Or remove the loop altogether (if possible), and use the EXISTS() function to know what you have to load instead of looping through each field.

12 Replies
Miguel_Angel_Baeyens
Not applicable

Re: Load table Where Exist ($(Variable that may be empty))

Use the condition when you create the variables, in the LET or SET statement instead of the EXISTS().

Maybe you could post a sample app to look into?

ericarru
Not applicable

Re: Load table Where Exist ($(Variable that may be empty))

Maybe You Refere to this?

Let vField1Name = FieldValue('Field1Name',$(iTable))& if(len(FieldValue('Field1Name',$(iTable)))=0,'a')

I Tryed also:

Let vField1Name =if(len(FieldValue('Field1Name',$(iTable)))=0,'a',  FieldValue('Field1Name',$(iTable)))

But it is not working: (. It dose not work because it says that field a does not exist. It whould work if it was exists('a','a') or exists('$(vField1Name)','$(vField1Name)').  But then doesent work if the field is the correct.

//**************************************

Here I show you some code:

Suppose this data on the QV

Table1:

[SomeKeyName1

A
B

C

]

Table2:

[SomeKeyName2,SomeKeyName3

D,1

E,2

F,3

]

Tables:

Load *

Inline [

TableName,Field1Name,Field2Name

Table1, SomeKeyName1

Table2,SomeKeyName2,SomeKeyName3

];

Data on QVD'S

Table1:

[SomeKeyName1

B

C

D

E

]

Table2:

[SomeKeyName2,SomeKeyName3

E,4

F,3

G,5

]

And now I am loading new data to concatenate on the tables so:

for iTable=1 to 2

     Let vTableName= FieldValue('TableName',$(iTable));

     Let vField1Name= FieldValue('Field1Name',$(iTable));

     Let vField2Name= FieldValue('Field2Name',$(iTable));

     Concatenate('$(vTablename)')

     Load *

     From [$(QVDPath)$(QVDName).qvd](qvd)

     Where not (Exist ( $(vField1Name), $(vField1Name) ) )

     and not (Exist ( $(vField2Name), $(vField2Name) ) )  // <-------- Here is the problem if vField2Name is Empty

            

The resullt have to be:

Table1:

[SomeKeyName1

A
B

C

D

E

]

Table2:

[SomeKeyName2,SomeKeyName3

D,1

E,2

F,3

E,4

G,5

]

pradosh_thakur
Not applicable

Re: Load table Where Exist ($(Variable that may be empty))

give it a try

Table:

Load *

Resident MyResidenTable

where not( Exist ( $(Field1NewTable), $(Field1OldTable) ) )

and not( Exist ( alt($(Field2NewTable),0), alt($(Field2OldTable),0 ) ) ;


or

Table:

Load *

Resident MyResidenTable

where not( Exist ( $(Field1NewTable), $(Field1OldTable) ) )

and not( Exist ( alt($(Field2NewTable),0), alt($(Field2OldTable),1 ) ) ;

Learning never stops.
ericarru
Not applicable

Re: Load table Where Exist ($(Variable that may be empty))

Hello, it seems pretty cool!

But when I try this is what I get:

Error in expression:

')' expected

MyTable:

LOAD *

From [..\QVD\MyTable.qvd](qvd)

Where  not( Exists(EVT_OCRD_TS,EVT_OCRD_TS))

and not( Exists(Alt(,'0'),Alt(,'0'))  )


pradosh_thakur
Not applicable

Re: Load table Where Exist ($(Variable that may be empty))

try this

Table:

Load *

Resident MyResidenTable

where not( Exist ( $(Field1NewTable), $(Field1OldTable) ) )

and not Exist ( alt($(Field2NewTable),0), alt($(Field2OldTable),0 ));

Learning never stops.
pradosh_thakur
Not applicable

Re: Load table Where Exist ($(Variable that may be empty))

i guess we missed one extra parenthesis at the last . add it and give it a go..

able:

Load *

Resident MyResidenTable

where not( Exist ( $(Field1NewTable), $(Field1OldTable) ) )

and not( Exist ( alt($(Field2NewTable),0), alt($(Field2OldTable),0 ) )) ;


or

Table:

Load *

Resident MyResidenTable

where not( Exist ( $(Field1NewTable), $(Field1OldTable) ) )

and not( Exist ( alt($(Field2NewTable),0), alt($(Field2OldTable),1 ) )) ;

Learning never stops.
ericarru
Not applicable

Re: Load table Where Exist ($(Variable that may be empty))

This whas the code when I got the error:

Table:

LOAD *

From [$(MyPath)$(MyTable).qvd](qvd)

Where  not( Exists($(vTimeField),$(vTimeField)))

and not( Exists(   Alt($(vField4),0), Alt($(vField4),0)    )    

pradosh_thakur
Not applicable

Re: Load table Where Exist ($(Variable that may be empty))

Table:

LOAD *

From [$(MyPath)$(MyTable).qvd](qvd)

Where  not( Exists($(vTimeField),$(vTimeField)))

and not( Exists(   Alt($(vField4),0), Alt($(vField4),0)    )  )


notice the last ')' for not it was not closed before

Learning never stops.
Miguel_Angel_Baeyens
Not applicable

Re: Load table Where Exist ($(Variable that may be empty))

Ok, so it is not that the field is empty, is that the field does not even exist for the step of the loop you are in, therefore the variable is never populated, with neither 0 or anything else, because you are in step 3 while you only have 2 values, so in the step 3 of the loop, the field does not exist in that table (not that it is empty).

In this case you will need to do two loads: one loading everything you need to load, the second with the EXISTS() in the second load. Or remove the loop altogether (if possible), and use the EXISTS() function to know what you have to load instead of looping through each field.