18 Replies Latest reply: Oct 17, 2012 10:31 AM by Thees Gieselmann RSS

    Crosstable numeric values

      Hey,

       

      I try to load an Excel Sheet using this Script.

      Unfortunately QV does not recognize the numeric rows appropriately. Tried to format them as text(...) but didnt help.

      The numbers are CustomerIDs on which sales data (Menge) is aggregated.

      I believe QV thinks they are the values in the table and not an ID.

       

      Directory;

      CrossTable(Debitor, Menge, 2)

      LOAD date(date#(Datum, 'MM/YYYY'), 'MMYYYY') as CalendarMonthAndYear,

           Zeile,

           10005,

           10104,

            ...........

           .................

            ...........

           90007,

           99999

      FROM

      KOST\201207.xls

      (biff, embedded labels, table is Tabelle3$, filters(

      Remove(Col, Pos(Top, 3))

      ));

       

      The same version worked with the customers actual name as ID instead of the numeric.

       

      Any Ideas? Or need additional information?

      Thx Thees

        • Re: Crosstable numeric values
          whiteline _

          QV does not recognize the numeric rows appropriately

          Hi.

          What do you mean ?

          I think it successfully creates a table with four fields:  CalendarMonthAndYear, Zeile, Debitor, Menge.

            • Re: Crosstable numeric values

              The table created is referenced to two other tables over the attributes "CalendarMonthAndYear" and "Debitor".

              In the data preview in the table-modell (the ctrl + t stuff) QV lists the "Debitor" Values under "Menge".

              "Menge" should contain the sales.

               

              When I create a Listbox with "Debitor" he lists all the Debitors twice. First those from the upper table, and Second those from the corresponding table (which relates the Debitors to a sales person).

              In the Listbox some are aligned right and some left. This is why I concluded there is a problem with the numeric format. Which is why he doesnt connect the two tables properly.

                • Re: Crosstable numeric values
                  whiteline _

                  Hi.

                   

                  Ok. Crosstable() statement always handles  headers as text. To transform your Debitor values to numbers use one more LOAD statement and num#() function.

                    • Re: Crosstable numeric values

                      Sry for late response aaaand thank you for your help

                       

                      How does such a LOAD statement need to look like?

                      Do you mean an INLINE LOAD?

                       

                      Unfortunately I cannot test this before tuesday next week but I definetly appreciate your help.

                        • Re: Crosstable numeric values
                          Miguel Angel Baeyens de Arce

                          Hi,

                           

                          As whiteline points, all field names converted to values in the Crosstable() load will be stored as literals of texts, instead of numbers. Dates in QlikView are numeric, so you need a second load to transform the values correctly. According to the following sample:

                           

                          DataTemp:
                          CROSSTABLE (Month, Value) LOAD ID,
                               January AS "01/01/2012",
                               February AS "01/02/2012",
                               March AS "01/03/2012"
                          FROM File.xls (...);
                          
                          STORE DataTemp INTO Temp.qvd (qvd);
                          
                          DROP TABLE DataTemp;
                          
                          Data:
                          LOAD ID,
                               Date(Date#(Month, 'DD/MM/YYYY')) AS ActualMonthDate,
                               Value
                          FROM Temp.qvd (qvd);
                          

                           

                          The first step is always the Crosstable() load, the second reads the same table but transforms the values into actual numeric values. Using QVD files you are likely to save time in the load process.

                           

                          Hope that helps.

                           

                          Miguel

                            • Re: Crosstable numeric values

                              Thank your very much. I will try a solution based on the given example.

                              Next tuesday i'll tell you wether it was successfull or if I am crying

                               

                              Besides that, why does everyone always create temporary tables?

                              Are there any Whitepapers or Blog-Reports on this topic? I am relatively new to QV and still need to figure out a lot of stuff

                                • Re: Crosstable numeric values
                                  Miguel Angel Baeyens de Arce

                                  Hi,

                                   

                                  They are not real temporary tables, rather the steps you need to follow in order to get the information clean and ready to use in the charts. Obviously, the less steps to take, the faster it will go, but usually the more complex it will be as well...

                                   

                                  Hope that helps.

                                   

                                  Miguel

                                    • Re: Crosstable numeric values

                                      Hi,

                                      Not sure if this has already been dealt with, but I hit this problem yesterday. I had to put square brackets around the Column headers, like so:

                                       

                                      CrossTable(Debitor, Menge, 2)

                                      LOAD date(date#(Datum, 'MM/YYYY'), 'MMYYYY') as CalendarMonthAndYear,

                                           Zeile,

                                           [10005],                // see these square brackets

                                           [10104],

                                            ...........

                                           .................

                                            ...........

                                           [90007],

                                           [99999]

                                      FROM

                                      KOST\201207.xls

                                      (biff, embedded labels, table is Tabelle3$, filters(

                                      Remove(Col, Pos(Top, 3))

                                      ));

                                        • Re: Crosstable numeric values

                                          @jonbroughavone: I read about that solution as well, but in a test it didn't work out well. Additionally the amount of columns I would have to adjust is too large to do manually.

                                          • Re: Crosstable numeric values
                                            Miguel Angel Baeyens de Arce

                                            Hi,

                                             

                                            You cannot do that in the same load statement, therefore the two loads I sampled above. Irrespective of what functions do you use, the Crosstable() transforms the field name (even if you are using a Date() function) into a string literal, that stores as a string in the value, so you need to transform it again.

                                             

                                            Hope that makes sense.

                                             

                                            Miguel

                                              • Re: Crosstable numeric values

                                                Hey,

                                                 

                                                after implementing the code provided my script looks way nicer and runs more smoothly

                                                 

                                                Still my problem remains, which makes me even more desperate because I have no clue where the error occurs from. I will try to describe my problem in better....:

                                                 

                                                export.png

                                                These are the tables I am currently working on.

                                                The SalesData is supposed to be be in the table "Data" (which is a crosstable)

                                                The table contains of several Excelsheets which are formatted in the same way only different/more/less "Debitor" (engl. debtor) Values. One sheet contains the data for one month. The script for the table looks like this:

                                                 

                                                Directory;
                                                Datatemp:
                                                CrossTable(Debitor, Menge, 2)
                                                LOAD date(date#(Datum, 'MM/YYYY'), 'MMYYYY') as CalendarMonthAndYear,
                                                     Zeile,
                                                     10005,
                                                     10104,
                                                ...............
                                                     90007,
                                                     99999
                                                FROM
                                                KOST\201207.xls
                                                (biff, embedded labels, table is Tabelle3$, filters(
                                                Remove(Col, Pos(Top, 3))
                                                ));

                                                STORE Datatemp into Temp.qvd (qvd);

                                                DROP Table Datatemp;

                                                Data:
                                                LOAD Zeile,
                                                Debitor,
                                                Menge
                                                FROM Temp.qvd (qvd);

                                                I execute this script for every month (with different debtor(Debitor) values for every month).

                                                 

                                                Now QlikView somehow "looses" the Data which results in a table "Data" containing the following:

                                                 

                                                DataContent.JPG

                                                As you can see, the value for "Debitor" (engl. debtor) is the same as "Menge" (engl. sum).

                                                My goal is to have the actual data in the column "Menge".

                                                 

                                                Thank you very much for the answers already provided, they helped a lot improving my understanding of QV

                                                  • Re: Crosstable numeric values
                                                    Miguel Angel Baeyens de Arce

                                                    Hi,

                                                     

                                                    Check the ammeded script, so the Debitor is always numeric after the crosstable:

                                                     

                                                    Directory;
                                                    Datatemp:
                                                    CrossTable(Debitor, Menge, 2)
                                                    LOAD date(date#(Datum, 'MM/YYYY'), 'MMYYYY') as CalendarMonthAndYear,
                                                         Zeile, 
                                                         10005, 
                                                         10104, 
                                                    ...............
                                                         90007, 
                                                         99999
                                                    FROM
                                                    KOST\201207.xls
                                                    (biff, embedded labels, table is Tabelle3$, filters(
                                                    Remove(Col, Pos(Top, 3))
                                                    ));
                                                    STORE Datatemp into Temp.qvd (qvd);
                                                    DROP Table Datatemp;
                                                    Data:
                                                    LOAD Zeile,
                                                    Num(Debitor) AS Debitor, // This is the new line
                                                    Menge
                                                    FROM Temp.qvd (qvd);
                                                    

                                                     

                                                    Hope that helps.

                                                     

                                                    Miguel

                                                      • Re: Crosstable numeric values

                                                        Only slow but steady we are getting ahead

                                                         

                                                        Now he seems to establish a correct relationship between the deptors. Still the value under "Menge" is the same number as the value for Debtor....it looks like this now:

                                                        DataContent1.JPG

                                                        Thus the old problem with my missing values remain. When I loaded the script with "CustomerXYZ" instead of the numeric customerID (Debitor) it worked fine. Only were several typos in the customer name which resulted in a loss of data.

                                                          • Re: Crosstable numeric values

                                                            Hi,

                                                            Again, not sure whether this targets your specific issue.

                                                            I had a problem with loading in a cross table where the column names, instead of being numeric, were strings that were too long and complex to test against and manipulate at later stages. They were the questions, options and answers from a survey monkey data dump.

                                                            I opted for not using embedded labels, but instead using "F" followed by the column number (F1...Fx) labels. I then set up a spreadsheet with the column headers, which I loaded in to a mapping table. I then performed an applymap() to replace the question and option labels, based upon the column position.

                                                            Though my mapping spreadsheet was static, I could presumably have created it from the header rows of a dynamic source spreadsheet.

                                                            Hopefully this makes sense and, if you're still struggling, gives a potential change of direction.

                                                            Jonathan

                                    • Re: Crosstable numeric values

                                      No new ideas how to solve this problem?