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: 
Not applicable

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

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

    

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

LOAD YEAR,

     Year_Month,

     Materiale

     Quantity,

     [Valore stk. v.]

FROM

    

     (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

21 Replies
Not applicable
Author

Hi,

Join seems indeed not to work copmletky as of secodn file. Way around is to first load all files in an internal table and then link to the main table

See file,

Jonathan

Not applicable
Author

Hi,

Everything works fine now!!!!!!!!!

Thanks for your time!!!!!

    Alberto Foret