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

[#1]

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

     

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

                                                        

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

[#2]

Hi,

Believe we missed a letter in the table name:

See attatchement.

Jonathan

[#3]

Hi,

When i run Test 3.qvw and load the scripts it gives me this error.

Know i'll send you script and sample data.

[#4]

Hi,

I've attached you a folder with everything.

Hope it helps...

Thanks a lot!!

   Alberto Foret

[#5]

Hi,

the issue is that you renamed the field.

I have loaded ot now without issue. You have teh replace in the file the path by your path (test3 in my file).

Be carefull with QV as it is case sensitive as well.

Jonathan

[#6]

Hi,

Did it work?

Regards,

Jonathan

[#7]

Hi,

Sorry for the delay, i've just tried the script and there's no error. But it seems like the field Quocient gives no result, i don't know why... Here there's an image of the table, i expected Quocient to give the same result as sum(VentaMKT)/Sum(Qta stk. val) but it doesn't. Do you have any idea about it?

Thanks a lot!!!!!!!

     Alberto Foret

[#8]

Hi,

I have results.

See file included.

Regards,

Jonathan

[#9]

Hi,

Few coments.

-If you make a list for YEAR_MONTH and Quocient, it only shows values for YEAR_MONTH = 201104.

-If you look at material 3034 for YEAR_MONTH = 201104, Quocient =0.222.... , VentaMKT = 2, [Qta stk. val] = 18. Then VentaMKT/[Qta stk. val] should be 0.1111..... and it shows 0.22222.....

Something isn't working wright. I think it might be the fact that the stock data has different table each month while the sales table is updated every month on the same table.

I don't really know how to solve this...

Thanks!!!

      Alberto Foret

[#10]

Hi Alberto,

Sounds to me this does not make sense. Why would you calculate the quotient based on stock of previous months?

Should you not have only 1 stockfile (current) and thus link only on material?

What is the purpose of your application?

Regards,

Jonathan

[#11]

Hi,

The purpose is to see the evolution of the stock rotation to make forecast of sales and buyouts. I've changed few thing in my Qlik to obtain data but there's still something wrong. Quocient gives you the same result as Sum(VentasMKT)/[Qta stk. val] instead of  Sum(VentasMKT)/Sum([Qta stk. val]). On the other hand this is OK!! But i've discovered the table is not giving the same result for Sum([Qta stk. val]) as for [Qta stk. val]....

Also Quocient is calculated only for the first table of the data not for the inventory of every month.

I suspect there's something wrong in loading one table for every month for the inventory.

It's driving me crazy!!!

Thank's a lot though!!!!

       Alberto Foret