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

1 Solution

Accepted Solutions
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

View solution in original post

21 Replies
Not applicable
Author

Hi,

Why don't you construct a key in both tables with Material and YearMonth and then make join on that?

Load Materiale & YEAR & Month(Year_month) as %Key from STocks.

Load Materiale & Year(Anno Messe) & Month(Anno Messe) as %Key from Vendes Client.

Then you can join both.

Regards,

Jonathan

Not applicable
Author

Hi Jonathan,

One of the problems is that [Anno Messe] isn't in date format, i load this file from an other department so i can't change it. That's why in the script i use wildmatch to match it with Year_month.

Is there a option to use a field like Year_month created in the same script for another table?

On the other side, i don't really understand how joining Materiale, [Anno Messe] and Year_month will allow me to create the new field Rotación. What was your thought for making the new field?

Thanks a lot!

Not applicable
Author

Hi,

For the format you need to reformat the field so that it gets in the same format. I guess it is fixed format? An alternative is that you have a differend table which does the reformatting for you. Something like:

Anno Mess                    MonthYear

JAN 2011                    012011

FEB 2011                   022011

Then you can do a join on Sales Table and Calendar (above one) which give you the monthyear field in right format.

Then last, if you have a key in both tables (Sales and Stock) you can join them in 1 table and therefor do your calculation as now the same material/month key will have both fields (stock and sales).

The load statement would be like this:

Sales:

Load * from Sales table;

Inner join

Load *, yearmonth & material as %key;

Load * from CalendaR ; //link to the new monthyear format

Inner join

Load *, calculated field (Sales/Stock) as ResultField;

Load *,yearmonth &  materiale as %key from Stock table;

You could also split the steps a bit more off and use resident load statements. Easier to debug.

Regards,

Jonathan

Not applicable
Author

Hi,

Which would be the statement in the script to join the to tables? How do i give a name to a loaded table? I'm sorry but i'm not used to work with scripts.

Thanks

Not applicable
Author

Hi,

Join is the statement (see help for details). Inner join will only keep does records which have an entry in both tables.

Giving a name is simple:

ThisIsMyTableName:

Load * from original table;

Later in script/layout you reference to  ThisIsMyTableName instead o fthe original name.

good luck,

Jonathan

R

Not applicable
Author

Hi,

I think i'm on the wright path, but i haven't achieved qlik to recognize the last expression Sales/Stock as Rotación.

My last well running script looks like this, could you base on my script to define the solution? The expression which should be introduced is: --> [Quantità PF] /[Qtà stk. val.]

TABLA_VENTAS_INICIAL:

LOAD Materiale,

     [Anno cal./mese],

     [Ricavi netti] as VentaMKT€,

     [Quantità PF] as VentaMKT,

     [Luogo di spedizione]

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

TABLA_STOCK_Y_VENTAS:

INNER JOIN(TABLA_VENTAS_INICIAL)

LOAD YEAR_MONTH,

     YEAR,

     [Anno cal./mese]

FROM (ooxml, embedded labels, table is Hoja1);

TABLA_STOCK:

LOAD YEAR,

     YEAR_MONTH,

     Materiale

     [Qtà stk. val.],

     [Valore stk. v.]

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

Not applicable
Author

I think you are almost there:

TABLA_VENTAS_INICIAL:

LOAD Materiale,

     [Anno cal./mese],

     [Ricavi netti] as VentaMKT€,

     [Quantità PF] as VentaMKT,

     [Luogo di spedizione]

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

INNER JOIN(TABLA_VENTAS_INICIAL)

LOAD YEAR_MONTH,

     YEAR,

     [Anno cal./mese]

FROM (ooxml, embedded labels, table is Hoja1);

Join (TABLA_VENTAS_INICIAL)

LOAD YEAR,

     YEAR_MONTH,

     Materiale

     [Qtà stk. val.],

     [Valore stk. v.]

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

TABLA_VENTAS_INICIA_NEW:

NOCONCATENATE

Load *, [Quantità PF] /[Qtà stk. val.] as [Quantità PF] /[Qtà stk. val.]

RESIDENT TABLA_VENTAS_INICIA;

Drop table TABLA_VENTAS_INICIA;

Regards,

JOnathan

Not applicable
Author

Hi,

The script gives me an error, it doesn't find the From: The error says exactly the following:

Error de sintaxis, cláusula FROM desplazada o no se encuentra la cláusula FROM:TABLA_VENTAS_INICIAL_NEW:

NOCONCATENATE

Load [Quantità PF] /[Qtà stk. val.] as [Quantità PF] /[Qtà stk. val.]

RESIDENT TABLA_VENTAS_INICIAL
TABLA_VENTAS_INICIAL_NEW:

NOCONCATENATE

Load [Quantità PF] /[Qtà stk. val.] as [Quantità PF] /[Qtà stk. val.]

RESIDENT TABLA_VENTAS_INICIAL

It says something like Sintax error, clause From displaced or not found.

I thought RESIDENT would....

Tell me if you know how to solve this.

Thanks a lot!!!!

Not applicable
Author

Hi,

Can you send the full script? And some saple data?

Thanks,

Jonathan