topic Creating a new field on script in QlikView

Creating a new field on script

Fri, 15 Apr 2011 16:13:53 GMT

Hi,

I am use a Qlikview which loads fields from different files. I have a file that has the inventory of every month, i load one table per month:

LOAD Año,

Mes,

Materiale,

[Qtà stk. val.],

[Valore stk. v.]

FROM

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

Also i have a file that loads the sells of every month per product:

LOAD Materiale,

IF(WildMatch([Anno cal./mese],'GEN*'), 'Enero',

IF(WildMatch([Anno cal./mese],'FEB*'), 'Febrero',

IF(WildMatch([Anno cal./mese],'MAR*'), 'Marzo',

IF(WildMatch([Anno cal./mese],'APR*'), 'Abril',

IF(WildMatch([Anno cal./mese],'MAG*'), 'Mayo',

IF(WildMatch([Anno cal./mese],'GIU*'), 'Junio',

IF(WildMatch([Anno cal./mese],'LUG*'), 'Julio',

IF(WildMatch([Anno cal./mese],'AGO*'), 'Agosto',

IF(WildMatch([Anno cal./mese],'SET*'), 'Septiembre',

IF(WildMatch([Anno cal./mese],'OTT*'), 'Octubre',

IF(WildMatch([Anno cal./mese],'NOV*'), 'Noviembre',

IF(WildMatch([Anno cal./mese],'DIC*'), 'Diciembre','ERROR')))))))))))) as Mes,

IF(WildMatch([Anno cal./mese],'*200*') or WildMatch([Anno cal./mese],'*199*'),'Antiguo',

IF(WildMatch([Anno cal./mese],'*2010'),'2010',

IF(WildMatch([Anno cal./mese],'*2011'),'2011',

IF(WildMatch([Anno cal./mese],'*2012'),'2012',

IF(WildMatch([Anno cal./mese],'*2013'),'2013',

IF(WildMatch([Anno cal./mese],'*2014'),'2014',

IF(WildMatch([Anno cal./mese],'*2015'),'2015','Actualizar Scrpit'))))))) as Año,

[Quantità PF] as Venta,

[Luogo di spedizione]

FROM

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

I want to create a field called 'Rotación' which is calculated as: --> (Venta/[Qtà stk. val.])

This field 'Rotación' has to be the sales (Venta) of a specific Year(Año) & Month(Mes) divided by the stock amount ([Qtà stk. val.]) of the same Year & Month. Also observe that the Month & Year, in the sales file, are calculated fields (to make it them same format as the month in the inventory file)

As the two fields that appear in the formula are provided from different files, i don't know how to make it in the script. Help!

Thanks for your time!!

Creating a new field on script

Fri, 15 Apr 2011 20:03:17 GMT

Hi Alberto

I don't understand why you have to create a new field in the script. I think it's better to display the rotation in a chart.

I suggest you to create a field date for each file :

From inventory : Makedate( Ano , Mes , 1 ) as Date

From sale : Makedate ( Year([Anno cal./mese]) , Month([Anno cal./mese]) , 1) as Date

So your 2 table are now linked by the date field.

Then create a chart with

Dimension : MonthNames (Date)

Expression : Sum (Venta) / Sum( [Qtà stk. val.])

Hope it helps you

Creating a new field on script

Mon, 18 Apr 2011 08:08:13 GMT

Hi,

I already have my Qlikview with few charts using the "Rotación" as expression. The fact is that "Rotación" is the most important parametre in my study, and i have to make pivot tables, charts and i also want to be able to select intervals in my "Rotacion" parameter.

If i achieve to create "Rotación" as a field, it would simplify my Qlikview giving me more options for study.

Does someone know how to create the field "Rotación" in the initial script??

Thanks again for your time!!