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

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

Anonymous — Thu, 04 Jan 2018 16:00:33 GMT

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?

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

Miguel_Angel_Baeyens — Thu, 04 Jan 2018 16:03:01 GMT

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?

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

Anonymous — Fri, 05 Jan 2018 07:24:47 GMT

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

]

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

]

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

]

Table2:

[SomeKeyName2,SomeKeyName3

D,1

E,2

F,3

E,4

G,5

]

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

pradosh_thakur — Fri, 05 Jan 2018 07:50:39 GMT

give it a try

Table:

Load *

Resident MyResidenTable

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

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

Table:

Load *

Resident MyResidenTable

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

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

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

Anonymous — Fri, 05 Jan 2018 08:48:15 GMT

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

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

pradosh_thakur — Fri, 05 Jan 2018 08:56:26 GMT

try this

Table:

Load *

Resident MyResidenTable

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

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

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

pradosh_thakur — Fri, 05 Jan 2018 08:58:37 GMT

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

Table:

Load *

Resident MyResidenTable

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

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

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

Anonymous — Fri, 05 Jan 2018 09:03:52 GMT

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

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

pradosh_thakur — Fri, 05 Jan 2018 09:06:04 GMT

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

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

Miguel_Angel_Baeyens — Fri, 05 Jan 2018 09:06:23 GMT

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.

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

Miguel_Angel_Baeyens — Fri, 05 Jan 2018 09:16:50 GMT

Actually according to this example, you don't need the loop at all to get the expected result:

Table1:

// LOAD statement for Table1

CONCATENATE LOAD *

FROM Table1.qvd (qvd); // or whatever the name it is for the Table1 concatenation

Table2:

// LOAD statement for Table2

CONCATENATE LOAD *

FROM Table2.qvd (qvd); // or whatever the name it is for the Table2 concatenation

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

Anonymous — Fri, 05 Jan 2018 09:27:27 GMT

When I add a new one QV is showing it to me in red, I counted them and all are closed, finally I tryed it and I got the same error.

I think that is because what it gets is Alt ( ,0).

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

Anonymous — Fri, 05 Jan 2018 10:45:10 GMT

You Are Right and I solved filling the table of the variable names with 0. If i do this, then i can use the Alt() function like

Exists( Alt($(vField2Name),'1'), Alt($(vField2Name),'1') ) and it works

Tables:

Load *

Inline [

TableName,Field1Name,Field2Name

Table1, SomeKeyName1,0,0

Table2,SomeKeyName2,SomeKeyName3

];

I would like both to give to both of you mbaeyens‌ and pradosh_thakur the correct answer but I can't.