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

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

Wed, 06 Jul 2011 15:30:34 GMT

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

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

Thu, 07 Jul 2011 07:56:24 GMT

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

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

Thu, 07 Jul 2011 09:47:40 GMT

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!

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

Thu, 07 Jul 2011 10:05:56 GMT

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

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

Thu, 07 Jul 2011 10:23:49 GMT

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

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

Thu, 07 Jul 2011 10:47:29 GMT

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

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

Thu, 07 Jul 2011 14:25:20 GMT

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]);

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

Thu, 07 Jul 2011 14:47:50 GMT

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

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

Fri, 08 Jul 2011 09:51:25 GMT

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!!!!

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

Fri, 08 Jul 2011 10:22:40 GMT

Hi,

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

Thanks,

Jonathan

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

Fri, 08 Jul 2011 10:27:33 GMT

Hi,

Believe we missed a letter in the table name:

See attatchement.

Jonathan

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

Fri, 08 Jul 2011 10:40:50 GMT

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.

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

Fri, 08 Jul 2011 10:58:51 GMT

Hi,

I've attached you a folder with everything.

Hope it helps...

Thanks a lot!!

Alberto Foret

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

Fri, 08 Jul 2011 11:37:56 GMT

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

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

Tue, 12 Jul 2011 13:21:04 GMT

Hi,

Did it work?

Regards,

Jonathan

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

Tue, 12 Jul 2011 13:45:35 GMT

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

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

Tue, 12 Jul 2011 14:36:05 GMT

Hi,

I have results.

See file included.

Regards,

Jonathan

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

Tue, 12 Jul 2011 15:02:54 GMT

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

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

Tue, 12 Jul 2011 15:12:10 GMT

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

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

Tue, 12 Jul 2011 15:46:31 GMT

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