9 Replies Latest reply: Mar 31, 2011 9:24 AM by Carlos Ayuso RSS

    Create a field on initial script

    Carlos Ayuso

      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

      [O:\Logistic\TEMAS STOCKS\INVENTARIOS\Informes mensuales\Informes Qlik\Informe Stocks 6A*.xlsx]

      (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
          CheenuJanakiram

          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
            CheenuJanakiram

            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

            [O:\Logistic\TEMAS STOCKS\INVENTARIOS\Informes mensuales\Informes Qlik\Informe Stocks 6A*.xlsx]

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

            • Create a field on initial script
              Carlos Ayuso

              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
                  CheenuJanakiram

                  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.,

                  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?

                  L

                    • Create a field on initial script
                      CheenuJanakiram

                      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
                          CheenuJanakiram

                          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
                          [O:\MARKETING\Qlik_MKT\Vendes\vendes clients*.xls]
                          (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
                          [O:\Logistic\TEMAS STOCKS\INVENTARIOS\Informes mensuales\Informes Qlik\Tipo de producto, peso, dimensiones y volumen.xlsx]
                          (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
                          [O:\Logistic\TEMAS STOCKS\INVENTARIOS\Informes mensuales\Informes Qlik\Informe Stocks 6A*.xlsx]
                          (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
                              Carlos Ayuso

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

                      • Create a field on initial script
                        Carlos Ayuso

                        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

                        [O:\MARKETING\Qlik_MKT\Vendes\vendes clients*.xls]

                        (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

                        [O:\Logistic\TEMAS STOCKS\INVENTARIOS\Informes mensuales\Informes Qlik\Tipo de producto, peso, dimensiones y volumen.xlsx]

                        (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

                        [O:\Logistic\TEMAS STOCKS\INVENTARIOS\Informes mensuales\Informes Qlik\Informe Stocks 6A*.xlsx]

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