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

[Anonymous]

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 $(Field2OldTable)  are 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?

[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]

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]

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

[Anonymous]

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]

try this

Table:

Load *

Resident MyResidenTable

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

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

[pradosh_thakur]

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

[Anonymous]

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]

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

[Miguel_Angel_Baeyens · Accepted solution]

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.