6 Replies Latest reply: Jan 12, 2016 1:41 AM by Andrey Khoronenko RSS

    Error loading from Excel QlikView Version 12.0

    Andrey Khoronenko

      Good day, dear colleagues!


      QlikView version 12.0.20000.0, Excel 2016.

      When loading of Excel file QlikView can not see the dates  with 01.10.2016. Dates until 01.09.2016 loading properly. What could be the problem?


      Yours faithfully


        • Re: Error loading from Excel QlikView Version 12.0
          Sunny Talwar

          Would you be able to share your sample Excel file? I can try test loading it into a new QVW to check what is going on. I have QlikView 12.0.

            • Re: Error loading from Excel QlikView Version 12.0
              Andrey Khoronenko

              If there is no record with the date 10/01/2016 on the sheet "Consumption" file Excel, record with the date 10/01/2016 from sheet "Coming" to QlikView not unloaded (red).

                • Re: Error loading from Excel QlikView Version 12.0
                  Sergey Polekhin

                  I have not see any problem with loading data from your file.

                  QV Application (v.12) example you can see in attach.

                   

                  I suppose you trying to load data from "Consumption" Tab (not from "Coming" Tab). And of course you will not see the 10/01/2016 date after this Load, because this date does not exists on the "Consumption" Tab.

                    • Re: Error loading from Excel QlikView Version 12.0
                      Andrey Khoronenko

                      Thanks for the answer. What caused this logic works QlikView? Why can not I use the sheet 2, without filling Sheet1?

                        • Re: Error loading from Excel QlikView Version 12.0
                          Sergey Polekhin

                          I think there are some misunderstanding and/or mixing different aspects in Data Loading logic. So my answer will have more detail:

                          1. You can load into QlikView any data you wish. But in the same time QlikView loads only data you exactly stated in the QlikView Load Script. And Load Script logic does not depend on Excel's file version or QlikView version.
                          2. So if you like to:
                            • Variant 1 - suppose you need to load data stored only on the "Consumption" excel's tab. In this case you need to choose the "Consumption" tab in Data Load Wizard and you will receive name "Consumption" placed in corresponding Data LOAD section in QlikView Load Script (see screenshoots below). Of course, DateOper field in resultant QlikView Data Model will contain only dates stored on "Consumption" excel's tab.

                                         1.JPG 2.JPG

                            • Variant 2 - suppose you need to load data stored only on the "Coming" excel's tab. In this case you need to choose the "Coming" tab in Data Load Wizard and you will receive name "Coming" placed in corresponding Data LOAD section in QlikView Load Script (see screenshoots below). Of course, DateOper field in resultant QlikView Data Model will contain only dates stored on "Coming" excel's tab.

                                         3.JPG 4.JPG

                            • Variant 3 - suppose you need to load data stored on both excel's tabs ("Consumption" and "Coming") . In this case you need to plase in one QlikView Load Script both Data Load sections (stated above). Of course, DateOper field in resultant QlikView Data Model will contain dates stored on both excel's tab. And in this case resultant QlikView Data Model will consist of two data tables automatically and correctly linked by using the QlikView synthetic key (composite key created from DateOper, Name and Summa fields).

                                         5.JPG

                              • For sure, during the Data Load you also can do any Data Transformations related to transforming only Data, transforming only Data Model (resultant tables) or transforming Data and Data Model.
                            • Re: Error loading from Excel QlikView Version 12.0
                              Andrey Khoronenko

                              Sergey, thank you for the detailed answer.

                               

                              No witchcraft, as usual, there is no. The error was in the code. Since I raised the topic, I will describe the order.

                               

                              This script code

                              Code1.jpg

                              Code2.jpg

                              After downloading the file represented by the above it got this data structure

                               

                              Data structure.jpg

                               

                              Forming dashboard

                               

                              Dashboard.jpg

                              The amount of red color field "Sum (SumComing)". Please note there is no current selections.

                               

                              But in the original amount of the other Excel file (the difference is highlighted in red below)!

                              Excel12.jpg

                              If on the sheet "Consumption" to add an entry with the date 01/10/2016, on dashboard everything is correct.

                               

                              Excel13.jpg

                              Dashboard1.jpg

                               

                              The error is contained in a line of code that defines the left join - not available the connection table.

                               

                              If the first table "Consumption" in this embodiment, the default code associated with the table "MasterCalendar", the second table "Coming" associated with the first "Consumption".  Therefore, the availability of the dates in the first table "Consumption" in the left-hand connection and is decisive for the date of the second table "Coming".

                               

                              Correct line of code 56 is replaced by the following

                               

                              Code3.jpg

                              Now everything is working properly.

                               

                              Once again thank you all for your answers.

                               

                              All the best