10 Replies Latest reply: Dec 17, 2014 10:24 AM by Friedrich Hofmann RSS

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

    Friedrich Hofmann


      `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

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

          could you please paste the script to check?

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

            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.

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

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

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

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

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

                                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.

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

                                    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