Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

datanibbler
Esteemed Contributor

Very urgent problem - load 11 lists where one field might be missing in 1?


`Hi,

I have a rather urgent problem: My colleague is not there and I actually have to do something else.

The issue is this:

- There are 11 lists on the server (Excel lists) which are usually equal in structure

- Those are currently loaded in a loop, so that we see which list we have an issue with in case we do.

- Just that is the case now: In one of those 11 lists, one field is just missing.

I have already developed a code to dynamically construct the fieldnames in case they vary - which they do from time to time - but I have no idea how to catch that possibility - that one field is present in 10 of the 11 lists, but not in the last one.

For now, it would perhaps be easier to just insert one field - which I cannot do, I have no RW access - but there has to be a possible long-term solution for this?

Thanks a lot!

Best regards,

DataNibbler

1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: Very urgent problem - load 11 lists where one field might be missing in 1?

Now then, this is actually possible.

What the following code does is inspect all of the data sources, get the full field list (all distinct field names over all sources), then create dynamic scripts for each load, marking missing fields so at least you can advise users why there's a problem.

//Inspect header for each list (this example loops over tabs in a single excel file)
For each tab in 'List1','List2','List3'
Labels:
First 1 LOAD Filename() as ListSource, '$(tab)' as TabSource, *
FROM
[Lists.xlsx]
(
ooxml, no labels, table is $(tab));

For l = 3 to NoOfFields('Labels') //first two fields are ListSource & TabSource
let f = FieldName($(l),'Labels');
LabelIndex:
Load ListSource as ListIndexSource, TabSource as TabIndexSource, FieldValue('$(f)',1) as Field resident Labels;
Next l;

Drop Table Labels;

next tab;

//Create dynamic script for each load
For each tab in 'List1','List2','List3'

//Create a table based on all possible fields
AllFields:
Load distinct Field as AllField resident LabelIndex;

//join in found fields for that source
left join (AllFields)
Load Field as AllField, Field as AllField2 resident LabelIndex where TabIndexSource='$(tab)';

//Create dynamic script making dummy field for missing
LoadScripts:
Load
'$(tab)'
as ScriptLoad,
concat(if(isNull(AllField2),chr(39) & 'field is missing' & chr(39) & ' as ' & AllField,AllField2),',') as Script
resident AllFields;// group by AllField;

Drop Table AllFields;

Next tab;

Drop Table LabelIndex;

//Now use scripts to load from sources using the dynamic script
For each tab in 'List1','List2','List3'

Let s = Lookup('Script','ScriptLoad','$(tab)','LoadScripts');

Data:
LOAD
'$(tab)'
as DataSource,
$(s)
FROM
[Lists.xlsx] (
ooxml, embedded labels, table is $(tab));

next tab;


You could miss the first step and when you create the AllFields table, use an inline list of expected fields instead if you don't want to do a dynamic check.

Another way to do this could be to use an ODBC connection and the SQLCOLUMNS command to see what it contains and use logic to handle that.

flipside

10 Replies
Not applicable

Re: Very urgent problem - load 11 lists where one field might be missing in 1?

could you please paste the script to check?

datanibbler
Esteemed Contributor

Re: Very urgent problem - load 11 lists where one field might be missing in 1?

Sure.

PFA.

I'm just thinking - I could use the code I have developed using the FIELDNAME() function - but that would mean I'd have to assume that if a specific field (by name) cannot be found in a specific place, it's not in the list - which is usually true, but still it's not my favourite ...

Thank you!

P.S.: The problem in this specific case is the field "VERSAND_SOLL" which is missing in one of the lists - there are 11 as I said, in the remaining 10, the field is there ... human error upon downloading the lists.

bwisenosimenkg
Valued Contributor

Re: Very urgent problem - load 11 lists where one field might be missing in 1?

Have them fix your lists. There is no point in tailoring you application to account for human errors if that will make the end result wrong anyway. Make the data entry employee accountable and have them fix it.

datanibbler
Esteemed Contributor

Re: Very urgent problem - load 11 lists where one field might be missing in 1?

Hi Simen,

yes, that is what would be logical - the issue is, this happens every other week or so - of course we can have somebody fix it every time (which is what my colleague usually does anyway) - but every time it happens, it means our QlikView_apps fail (and there are quite a few triggered by the completion of this one, which consequently do not run) - that means we must start it and it's about an hour late.

Moreover - more of a problem actually - what management sees, if they look at it, is that the QlikView_apps keep failing. They don't see the reason, so we as QlikView_developers are in the first line then. Of course we can always explain in cases like that, but it's the impression that counts.

Re: Very urgent problem - load 11 lists where one field might be missing in 1?

Hi DataNibbler,

there isn't any way to react on wrong or missing fields inside from a load. This meant you need to react from outside a load. You could use errormode to skip possible errors and create an error-message or you loads your files in a loop to check exists the file and which fields are available (load first 1 without embedded lables and you could check these values against a defined field-list) and you could decide how to react.

But the best way is to have access to the files and/or avoid these human mistakes generally (macro checks on validity inside the excel - and is something is wrong no closing and storing from the file is possible).

- Marcus

datanibbler
Esteemed Contributor

Re: Very urgent problem - load 11 lists where one field might be missing in 1?

Hi Marcus,

how can I check whether a field exists? What function would that be?

I have thought about using the FIELDNAME() function for this, but considering that the field might be in another place, that would become too complicated ...

Re: Very urgent problem - load 11 lists where one field might be missing in 1?

I mean something like this:

CheckTable:

First 1 Load * From xls (biff, no lables, table is Sheet1);

for i = 1 to nooffields('CheckTable')

     let vFields = '$(vFields)' & fieldname($(i), 'CheckTable') & ', ';

next

if '$(vFields)' = 'YourDefinedFieldList' then

     Load ...

else

     do something

end if

- Marcus

datanibbler
Esteemed Contributor

Re: Very urgent problem - load 11 lists where one field might be missing in 1?

Hi Marcus,

thanks for that! I realize that it's immensely complicated to account for the possibility of some field missing in the list. I'll talk about it with my colleague, however.

flipside
Valued Contributor II

Re: Very urgent problem - load 11 lists where one field might be missing in 1?

Now then, this is actually possible.

What the following code does is inspect all of the data sources, get the full field list (all distinct field names over all sources), then create dynamic scripts for each load, marking missing fields so at least you can advise users why there's a problem.

//Inspect header for each list (this example loops over tabs in a single excel file)
For each tab in 'List1','List2','List3'
Labels:
First 1 LOAD Filename() as ListSource, '$(tab)' as TabSource, *
FROM
[Lists.xlsx]
(
ooxml, no labels, table is $(tab));

For l = 3 to NoOfFields('Labels') //first two fields are ListSource & TabSource
let f = FieldName($(l),'Labels');
LabelIndex:
Load ListSource as ListIndexSource, TabSource as TabIndexSource, FieldValue('$(f)',1) as Field resident Labels;
Next l;

Drop Table Labels;

next tab;

//Create dynamic script for each load
For each tab in 'List1','List2','List3'

//Create a table based on all possible fields
AllFields:
Load distinct Field as AllField resident LabelIndex;

//join in found fields for that source
left join (AllFields)
Load Field as AllField, Field as AllField2 resident LabelIndex where TabIndexSource='$(tab)';

//Create dynamic script making dummy field for missing
LoadScripts:
Load
'$(tab)'
as ScriptLoad,
concat(if(isNull(AllField2),chr(39) & 'field is missing' & chr(39) & ' as ' & AllField,AllField2),',') as Script
resident AllFields;// group by AllField;

Drop Table AllFields;

Next tab;

Drop Table LabelIndex;

//Now use scripts to load from sources using the dynamic script
For each tab in 'List1','List2','List3'

Let s = Lookup('Script','ScriptLoad','$(tab)','LoadScripts');

Data:
LOAD
'$(tab)'
as DataSource,
$(s)
FROM
[Lists.xlsx] (
ooxml, embedded labels, table is $(tab));

next tab;


You could miss the first step and when you create the AllFields table, use an inline list of expected fields instead if you don't want to do a dynamic check.

Another way to do this could be to use an ODBC connection and the SQLCOLUMNS command to see what it contains and use logic to handle that.

flipside

Community Browser