topic Checking if field exists to Qualify in QlikView

Checking if field exists to Qualify

tom2qlik — Thu, 30 Mar 2017 10:31:59 GMT

Hi All,

I have a Qlik app where I am loading in many tables with the share the same field name. I want to qualify only those field names which have a match from what's already been loaded into qlikview. So I'm comparing the current field to the pre-loaded fields in each table.

I have attempted the solution however I am getting errors so I'm clearly going wrong somewhere.

Here are my attempts:

Attempt 1

Sub LoadQVDData

For each file in FileList(vDatasource&'*.qvd')
FOR i = 1 to NoOfFields('file')
Load FieldName($(i),'file') as CurrentFieldName
From [$(file)](qvd);

For each table in [$Table]
FOR j = 1 to NoOfFields('table')
if (CurrentFieldName = FieldName($(j),'file',)
QUALIFY CurrentFieldName, CurrentFieldName)
AutoGenerate 1;
Next j;
Next table;

AutoGenerate 1;
Next i;
Next file;
ENDSUB;

Attempt 2

Sub LoadQVDData

FOR each file in FileList(vDatasource&'*.qvd')
FOR i = 1 to NoOfFields('file')
   FOR each table in [$Table]
    FOR j = 1 to NoOfFields('table')

      if (FieldName($(i),'file') = FieldName($(j),'file'),

        Load Qualify FieldName($(i),'file')
     From [$(file)](qvd),
        Load FieldName($,'file')
    From [$(file)](qvd))

AutoGenerate 1;
Next j;
Next table;
AutoGenerate 1;
Next i;

Next file;
ENDSUB;

Thanks,

Tom

Re: Checking if field exists to Qualify

marcus_sommer — Thu, 30 Mar 2017 11:09:57 GMT

I'm not sure if your aim to qualify the matching fieldnames between multiple tables is useful - especially with an automatic routine. Of course you will avoid synthetic keys with it but I doubt that you will get a with it a well working datamodel.

For this you should know each of your fields and set all the associations explicitly and it could be that it would be better to merge some tables and other might be reduced and linked over some extra tables.

If you want to stay by a qualify-approach it's common to use following logic:

QUALIFY *;

UNQUALIFY Key1, Key2;

load ....

UNQUALIFY *;

- Marcus

Re: Checking if field exists to Qualify

tom2qlik — Thu, 30 Mar 2017 13:39:58 GMT

The idea was to unqualify those that I knew were joins. Your method is probably better but I would still like to see if it can be achieved.

Re: Checking if field exists to Qualify

marcus_sommer — Thu, 30 Mar 2017 14:18:32 GMT

For me it made not much sense and you will need quite a lot of efforts to implement such a solution. The reason for this is you will need a load to qualify these fields - you couldn't do it after the loading with a rename-statement without getting the same error which occured here: Renaming Fields to names that already exists.

This meant you would need to load each file twice - at first only the header to get all included fieldnames and in following routines you would need to check if this fieldname occured already and if it's found a second time to add this field to a qualify-string-variable which you would use afterwards within the qualify-statement (alternatively would be to create a complete dynamical load-statement (with an Field AS Table.Field statement) on the fly within a variable and execute the afterwards).

Of course this is possible and I have done something like this more than once but it's not quite trivial and like above mentioned there would be no benefit - rather the opposite (despite from the fact that you would learn a lot).

- Marcus