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: 
Anonymous
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

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.

View solution in original post

12 Replies
Miguel_Angel_Baeyens

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?

Anonymous
Not applicable
Author

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
Master II
Master II

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.
Anonymous
Not applicable
Author

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
Master II
Master II

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
Master II
Master II

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.
Anonymous
Not applicable
Author

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
Master II
Master II

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

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.