topic Create a field on initial script in QlikView

Create a field on initial script

Wed, 30 Mar 2011 13:07:23 GMT

Hi,

I want to create a field on the initial script using two fields from two different files.

a part of my script looks like this:

LOAD Año,

Mes,

Materiale,

[Qtà stk. val.],

[Valore stk. v.],

---->IF([Qtà stk. val.]=0,'INF',Venta/[Qtà stk. val.]) as Rotación,

---->IF([Qtà stk. val.]=0,'No Valorable',

IF(Venta/[Qtà stk. val.] >= 10,'Stock Insuficiente',

IF(Venta/[Qtà stk. val.] >= 3 and Venta/[Qtà stk. val.] <= 10, 'Correcto',

IF(Venta/[Qtà stk. val.] >= 1 and Venta/[Qtà stk. val.] <= 3,'Stock Elevado',

IF(Venta/[Qtà stk. val.] >=0.25 and Venta/[Qtà stk. val.] <= 1,'Rotación Defectuosa',

IF(Venta/[Qtà stk. val.] >= 0 and Venta/[Qtà stk. val.] <= 0.25, 'Rotación Muy Defectuosa','No Valorable')))))) as [Situación Stock],

[tarifa almacenaje],

[tarifa manipulación],

[Coste standard]

FROM

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

The problem is that the field Venta comes from another file so the script gives an error.

How can i do to create the field on the initial script using two fields loaded from two different files?

Thanks for your time!!

Create a field on initial script

Wed, 30 Mar 2011 13:11:36 GMT

Does Venta have a matching value in this table? If yes, you can use the mapping load technique to load the Venta into this table and then a preceding load technique for those if-statements.

Do you need more info on mapping loads and preceding load technique. Mapping load is the same as an Excel vlookup.

Create a field on initial script

Wed, 30 Mar 2011 13:15:33 GMT

I don't really know how to use mappingload techniques, could you expain me or link me somewhere so i can check it?

Thanks for your time!!

Create a field on initial script

Wed, 30 Mar 2011 13:16:33 GMT

Your script should look something like this:

MappingLoadTableName:
MAPPING LOAD
FieldThatLinksVentaToTableBelow,
Venta
From Wherever;

FinalTable:
LOAD *,

IF([Qtà stk. val.]=0,'INF',Venta/[Qtà stk. val.]) as Rotación,

IF([Qtà stk. val.]=0,'No Valorable',

IF(Venta/[Qtà stk. val.] >= 10,'Stock Insuficiente',

IF(Venta/[Qtà stk. val.] >= 3 and Venta/[Qtà stk. val.] <= 10, 'Correcto',

IF(Venta/[Qtà stk. val.] >= 1 and Venta/[Qtà stk. val.] <= 3,'Stock Elevado',

IF(Venta/[Qtà stk. val.] >=0.25 and Venta/[Qtà stk. val.] <= 1,'Rotación Defectuosa',

IF(Venta/[Qtà stk. val.] >= 0 and Venta/[Qtà stk. val.] <= 0.25, 'Rotación Muy Defectuosa','No Valorable')))))) as [Situación Stock];

LOAD Año,

applymap('MappingLoadTableName', FieldThatLinksVentaToTableBelow, 0) as Venta,

Mes,

Materiale,

[Qtà stk. val.],

[Valore stk. v.],

[tarifa almacenaje],

[tarifa manipulación],

[Coste standard]

FROM

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

Create a field on initial script

Wed, 30 Mar 2011 14:30:49 GMT

Hi,

I really didn't understand the mapping load procedure.

In the first file I am loading, the sales evolution for every material, I have three fields which I will use:

Materiale, Venta and two calculated fields called Año and Mes.

On the second file I load, the inventory file, I have:

Año, Mes, Materiale and [Qtà stk. val.] (Amount of product in the inventory that month)

My purpose is that when Año, Mes and Materiale Match in both files, create a new column called "Rotación" that looks like:

IF([Qtà stk. val.]=0,'INF',Venta/[Qtà stk. val.]) as Rotación,

This procedure is to avoid taking the sales to the inventory file and then calculating the rotación.

I would introduce the formula as an expression inside the object, but i will need this field (Rotación) for few pages and i prefer not to copy the expression every time i need it.

I hope I have expressed myself better this time.

Thanks very much!!

Create a field on initial script

Wed, 30 Mar 2011 15:01:14 GMT

Ah ok.

An applymap is the same as an Excel vlookup. So let us say I have a Product table with ProductID and ProductPrice and I have a Sales table with ProductID and QuantitySold, I might want to find SalesValue such as ProductPrice * QuantitySold within the SalesTable. The problem in this case example is that ProductPrice is in Product table and I need it in the Sales table.

In this case, I can return the ProductPrice in the Sales table via an applymap. Such as

MappingPriceTable:

ProductID, ProductPrice

From Product;

SalesTable:

applymap('MappingPriceTable', ProductID, 0) * QuantitySold as SalesValue,

etc.,

From Sales;

What I understand of what you are trying to do is a comparison of values between 2 tables. When the values are the same in Table1 and Table2, then calculate something else. Is this right?

If you are trying to do this then the exists function would be useful.

exists(X, Y) does a comparison of values in Field X and values in Field Y. When the condition is fulfilled (when there is a match), QV returns a Boolean of -1 and when the condition is not fulfilled it returns zero.

Hence, I think you want to load a concatenated field in your first table, such as:

Año & Mes & Materiale as Matchingfield, etc...

And in the second table do a preceding load statement such as:

LOAD *,

if(MatchingField = -1, then EXPRESSION, 0) as Rotacion;

LOAD

Año & Mes & Materiale as Matchingfield,

OriginalField,

NextOriginalField,

etc...

Does this make sense. If no, can you give a text file with the script of BOTH tables?

Create a field on initial script

Wed, 30 Mar 2011 15:05:23 GMT

Sorry mistake:

This is your load statement:

Table one add one field, such as:

Año & Mes & Materiale as Matchingfield1, etc...

And in the second table add this:

LOAD *,

if(MatchingField = -1, then EXPRESSION, 0) as Rotacion;

LOAD *,

exists(MatchingField1, MatchingField2) as MatchingField;

LOAD

Año & Mes & Materiale as Matchingfield2,

OriginalField,

NextOriginalField,

etc...

Create a field on initial script

Wed, 30 Mar 2011 15:23:19 GMT

My complete script is as follows,

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

FROM

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

LOAD Materiale,

BL,

[Nombre Materiale],

[Tarifa Almacenaje (€/m3)],

SM as SITUACIÓN,

Familia,

[volume M3],

IF((BL=10 AND Familia <> 99), 'AQS',

IF((BL=10 AND Familia = 99), 'AQS ACC',

IF((BL=40 AND Familia <> 99), 'CAL',

IF((BL=40 AND Familia = 99), 'CAL ACC',

IF((BL=85 AND Familia <> 99), 'RIN',

IF((BL=85 AND Familia = 99), 'RIN ACC','OTROS')))))) as Clasificación

FROM

(ooxml, embedded labels, table is [Datos Materiales]);

LOAD Año,

Mes,

Materiale,

[Qtà stk. val.],

[Valore stk. v.],

[tarifa almacenaje],

[tarifa manipulación],

[Coste standard]

FROM

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

This is the expressions i want to introduce:

IF([Qtà stk. val.]=0,'INF',Venta/[Qtà stk. val.]) as Rotación,

IF([Qtà stk. val.]=0,'No Valorable',

IF(Venta/[Qtà stk. val.] >= 10,'Stock Insuficiente',

IF(Venta/[Qtà stk. val.] >= 3 and Venta/[Qtà stk. val.] <= 10, 'Correcto',

IF(Venta/[Qtà stk. val.] >= 1 and Venta/[Qtà stk. val.] <= 3,'Stock Elevado',

IF(Venta/[Qtà stk. val.] >=0.25 and Venta/[Qtà stk. val.] <= 1,'Rotación Defectuosa',

IF(Venta/[Qtà stk. val.] >= 0 and Venta/[Qtà stk. val.] <= 0.25, 'Rotación Muy Defectuosa','No Valorable')))))) as [Situación Stock], "

Hope this helps!!!!

Create a field on initial script

Wed, 30 Mar 2011 16:14:43 GMT

Hi,

It's difficult giving you a scripting technique that will universally work, especially when we are talking theory as I don't know what your underlying data structure is, but I think you might be trying something along the lines of this. Can you test this script? Tell me if it works at least and it returns the values you want. Then maybe it might be easier to explain the scripting techniques, as there are different ways around this. I am writing the script without knowing the amount of rows you are loading, content & cardinality of data, hence I cannot ensure that this script is optimally written, but I think it will generate the numbers you are looking for. Let me know.

Hoja:
LOAD *,
Año & Mes & Materiale as LinkField;
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
FROM

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

Venta_Map:
MAPPING LOAD
LinkField,
Venta
Resident Hoja;

DatosMateriales:
LOAD Materiale,
BL,
[Nombre Materiale],
[Tarifa Almacenaje (€/m3)],
SM as SITUACIÓN,
Familia,
[volume M3],
IF((BL=10 AND Familia <> 99), 'AQS',
IF((BL=10 AND Familia = 99), 'AQS ACC',
IF((BL=40 AND Familia <> 99), 'CAL',
IF((BL=40 AND Familia = 99), 'CAL ACC',
IF((BL=85 AND Familia <> 99), 'RIN',
IF((BL=85 AND Familia = 99), 'RIN ACC','OTROS')))))) as Clasificación
FROM

(ooxml, embedded labels, table is [Datos Materiales]);

InventarioMes_Temp:
LOAD Año,
Mes,
Materiale,
Año & Mes & Materiale as LinkField,
[Qtà stk. val.],
[Valore stk. v.],
[tarifa almacenaje],
[tarifa manipulación],
[Coste standard]
FROM

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

InventarioMes:
LOAD
*,
IF([Qtà stk. val.]=0,'INF',Venta/[Qtà stk. val.]) as Rotación,
IF([Qtà stk. val.]=0,'No Valorable',
IF(Venta/[Qtà stk. val.] >= 10,'Stock Insuficiente',
IF(Venta/[Qtà stk. val.] >= 3 and Venta/[Qtà stk. val.] <= 10, 'Correcto',
IF(Venta/[Qtà stk. val.] >= 1 and Venta/[Qtà stk. val.] <= 3,'Stock Elevado',
IF(Venta/[Qtà stk. val.] >=0.25 and Venta/[Qtà stk. val.] <= 1,'Rotación Defectuosa',
IF(Venta/[Qtà stk. val.] >= 0 and Venta/[Qtà stk. val.] <= 0.25, 'Rotación Muy Defectuosa','No Valorable')))))) as [Situación Stock]
;
LOAD
*,
applymap('Venta_Map', LinkField, 0) as Venta
Resident InventarioMes_Temp;

drop table InventarioMes_Temp;

Create a field on initial script

Thu, 31 Mar 2011 13:24:27 GMT

Hi,

I've just tried the script and it doesn't work. It gives two values for every Materiale, Año & Mes. The Values are allways "-" & "0", as you can observe on the image below.

As you can see, the script has separated the values of Cantidad & Venta for each materiale in two different registers. I imagine that is why "Rotación" gets only values "-" &"0".

I don't really know where the problem is in the script...

Anyway, thanks for trying!!!!!