21 Replies Latest reply: Jul 14, 2011 10:19 AM by Carlos Ayuso RSS

    How to make new field in srcript from two different tables. HELP PLEASE!

    Carlos Ayuso

      Hi,

       

      I've been carrying this problem from long time ago and i would be very pleased to find a solution.

       

      I have two different tables wich i load in my QV. Monthly Stock Tables and a Annual Sales table as in the following example:

       

                MONTHLY STOCK TABLES                                                   ANNUAL SALES TABLE

        

            1TABLAS.jpg

       

      My intention is to create a table that uses the field Quantity and the field Sales to calculate a new field Rotacion. This new field is calculated as [Sales/Quantity]. And the table should be as follows

       

                                                                              

                                                                                           ROTACION TABLE

       

                                                               2Tablas.jpg

       

      One of the problems is the date format, which is different in both initial tables. The other problem is that the stock tables are load individually.

       

      In my actual script i load this tables as follows:

       

       

      LOAD Materiale,

           IF(WildMatch([Anno Messe],'*200*') or WildMatch([Anno Messe],'*199*'),'Antiguo',

            IF(WildMatch([Anno Messe],'*2010'),'2010',

             IF(WildMatch([Anno Messe],'*2011'),'2011',

              IF(WildMatch([Anno Messe],'*2012'),'2012',

               IF(WildMatch([Anno Messe],'*2013'),'2013',

                IF(WildMatch([Anno Messe],'*2014'),'2014',

                 IF(WildMatch([Anno Messe],'*2015'),'2015','Actualizar Scrpit'))))))) as YEAR,

                  IF(WildMatch([Anno Messe],'GEN*'), right([Anno Messe],4)&01,

                   IF(WildMatch([Anno Messe],'FEB*'), right([Anno Messe],4)&02,

                    IF(WildMatch([Anno Messe],'MAR*'), right([Anno Messe],4)&03,

                     IF(WildMatch([Anno Messe],'APR*'), right([Anno Messe],4)&04,

                      IF(WildMatch([Anno Messe],'MAG*'), right([Anno Messe],4)&05,

                       IF(WildMatch([Anno Messe],'GIU*'), right([Anno Messe],4)&06,

                        IF(WildMatch([Anno Messe],'LUG*'), right([Anno Messe],4)&07,

                         IF(WildMatch([Anno Messe],'AGO*'), right([Anno Messe],4)&08,

                          IF(WildMatch([Anno Messe],'SET*'), right([Anno Messe],4)&09,

                           IF(WildMatch([Anno Messe],'OTT*'), right([Anno Messe],4)&10,

                            IF(WildMatch([Anno Messe],'NOV*'), right([Anno Messe],4)&11,

                             IF(WildMatch([Anno Messe],'DIC*'), right([Anno Messe],4)&12,'ERROR')))))))))))) as Year_Month,

           [Ricavi netti] as VentaMKT€,

           Sales as VentaMKT,

           [Luogo di spedizione]

      FROM

           [O:\Logistic\Reporting Logistica\Qlikview Flows Report\Datos Report\vendes clients*.xls]

           (biff, embedded labels, table is Hoja1$);

       

      LOAD YEAR,

           Year_Month,

           Materiale

           Quantity,

           [Valore stk. v.]

      FROM

           [O:\Logistic\Reporting Logistica\Qlikview Flows Report\Datos Report\Informe Stocks 6A*.xlsx]

           (ooxml, embedded labels, table is [Inventario mes])

       

       

      I hope someone can help me solve this problem. As i need to use Rotacion for tables, graphs charts and more it's necessary to have this parameter as a field, otherwise i would calculate the parameter on a expression.

       

      If there's something isn't clear, please complain and i'll try to explain myself better.

       

      THANKS A LOT!!!!!

       

           Alberto Foret