Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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!
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
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
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
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
TABLA_STOCK_Y_VENTAS:
INNER JOIN(TABLA_VENTAS_INICIAL)
LOAD YEAR_MONTH,
YEAR,
[Anno cal./mese]
FROM
TABLA_STOCK:
LOAD YEAR,
YEAR_MONTH,
Materiale,
[Qtà stk. val.],
[Valore stk. v.]
FROM
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
INNER JOIN(TABLA_VENTAS_INICIAL)
LOAD YEAR_MONTH,
YEAR,
[Anno cal./mese]
FROM
Join (TABLA_VENTAS_INICIAL)
LOAD YEAR,
YEAR_MONTH,
Materiale,
[Qtà stk. val.],
[Valore stk. v.]
FROM
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
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!!!!
Hi,
Can you send the full script? And some saple data?
Thanks,
Jonathan