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: 
datanibbler
Champion
Champion

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
Partner - Specialist II
Partner - Specialist II

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

View solution in original post

10 Replies
Not applicable

could you please paste the script to check?

datanibbler
Champion
Champion
Author

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.

simenkg
Specialist
Specialist

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
Champion
Champion
Author

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.

marcus_sommer

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
Champion
Champion
Author

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

marcus_sommer

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
Champion
Champion
Author

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
Partner - Specialist II
Partner - Specialist II

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