Formula excel

[Mayda]

Buen día! / Good morning 

Tengo una duda, como puedo pasar esta formula de excel en el editor de la carga de datos.

he cargado la tabla de datos Productos en Qlik y de la Columna $A$3:$H$10000;7; hay datos que se tienen que tomar en cuenta también, si todas las columnas cuentan con datos se contesta con un Si, pero si en una o mas columnas no tienen datos se contesta con un "No Existe".

=SI(F8="";""  ;SI.ERROR(BUSCARV(H8;Productos!$A$3:$H$10000;7;FALSO);"No Existe")) 

donde F8 es una FECHA y H8 es un numero de producto.

-------------------------------------------------------------------------------------------------------------------------

I have a question, how can I pass this excel formula in the data load editor.

loaded the Products data table into Qlik and Column $A$3:$H$10000;7; there is data that must also be taken into account, if all the columns have data, the answer is Yes, but if one or more columns do not have data, the answer is "Does not exist".

=IF(F8="";"" ;IF.ERROR(VLOOKUP(H8;Products!$A$3:$H$10000;7;FALSE);"Does not exist"))

where F8 is a DATE and H8 is a product number.

Gracias

[JordyWegman]

Hi Mayda,

Maybe for other people you can list your question in English.

But you can do this with a mapping table. A mapping table can return a value when the product is not found.

mapProduct2Product:
Mapping
Load
    PRODUCT,
    PRODUCT
FROM [YourSource];

Table:
Load
   IF( Trim( DATE  & '' ) = '' // I use the trim to be sure it's empty
        , ''
        , ApplyMap( 'mapProduct2Product', PRODUCT , 'Does not exist')
   )                                       as [YourNewField]
FROM [Your Source];

 Jordy

Climber

[Mayda]

Hi  @JordyWegman 

In the condition I need to include 7 columns of data from the products table, if they have data, the function is fulfilled with a 'Yes' but if in one or more columns there is no data, it is answered with a 'Does not exist'

Thanks

[JordyWegman]

Hi Mayda,

For this you can adjust the mapping table so you only include all things that have data.

mapProduct2Product:
Mapping
Load
    PRODUCT,
    PRODUCT
FROM [YourSource]
Where Trim( Field1 ) & '' <> ''
  and Trim( Field2 ) & '' <> ''
  and Trim( Field3 ) & '' <> ''
  and Trim( Field4 ) & '' <> ''
  and Trim( Field5 ) & '' <> ''
  and Trim( Field6 ) & '' <> ''
  and Trim( Field7 ) & '' <> ''
;

Table:
Load
   IF( Trim( DATE  & '' ) = '' // I use the trim to be sure it's empty
        , 'Yes'
        , ApplyMap( 'mapProduct2Product', PRODUCT , 'Does not exist')
   )                                       as [YourNewField]
FROM [Your Source];

If you have some sample data or app that would help in this situation.

Jordy

Climber

[Mayda]

Hi @JordyWegman 

I have a doubt ,  the data is from 2 different tables and I have to make one I pass the data to you.

[PRODUCTOS]:
LOAD
No_ As "Cod_Producto_Num",
Description As Descripción_Alias,
"Search Description" As Descripción,
"Description 2",
"Unit Cost" AS Coste_Unitario,
If(Blocked='1', 'Si','No') As Bloqueado,
"Manufacturer Code" AS Cód._Fabricante,
"Item Category Code",
"Product Group Code" As Cod_Categoría_Producto,
"Item Tracking Code" As Cod._Seguim_Prod,
"No_ cliente" AS ID_CLIENTE,
"Referencia cliente",
Tecnología,
if(Propio= '1', 'Si', 'No') As Propio,
"Version Software"
FROM [lib://N_QVD/ITEM.qvd](qvd)
Where "Item Category Code" = 'E';

LEFT JOIN [PRODUCTOS]:
LOAD
FABRICANTE,
MODELO,
CONECTIVIDAD,
CONTACTLESS,
FAMILIA,
TIPO_PCI
FROM [lib://DATASET_N/Stock.qvd](qvd);

[PRODUCTOS_Temp]:
Load
"Cod_Producto_Num",
Descripción,
Descripción_Alias,
"Description 2",
Coste_Unitario,
Bloqueado,
Cód._Fabricante,
"Item Category Code",
Cod_Categoría_Producto,
Cod._Seguim_Prod,
ID_CLIENTE,
"Referencia cliente",
TECNOLOGIA2,
Propio,
"Version Software",
FABRICANTE,
MODELO,
CONECTIVIDAD,
CONTACTLESS,
FAMILIA,
TIPO_PCI
RESIDENT [PRODUCTOS];
DROP TABLE [PRODUCTOS];
RENAME TABLE [PRODUCTOS_Temp] to [PRODUCTOS];

How can I do the mapping with this data??

[JordyWegman]

I think this makes it easier while you can join the fields.

[PRODUCTOS]:
LOAD
*
FROM [lib://N_QVD/ITEM.qvd](qvd)
Where "Item Category Code" = 'E';


LEFT JOIN [PRODUCTOS]:
LOAD
     *
FROM [lib://DATASET_N/Stock.qvd](qvd);

[PRODUCTOS_Temp]:
Load
     *
     IF( Trim( FABRICANTE ) & '' = ''
         , 'Does not exist'
         , IF( Trim( MODELO) & '' = ''
            , 'Does not exist'
            ,IF( Trim( CONECTIVIDAD) & '' = ''
               , 'Does not exist'
               ,IF( Trim( CONTACTLESS) & '' = ''
                  , 'Does not exist'
                  ,IF( Trim( FAMILIA) & '' = ''
                      , 'Does not exist'
                        ,IF( Trim( TIPO_PCI) & '' = ''
                         , 'Does not exist'
                         , 'Yes'
                        )
                   )
              )
          )
      )
)

RESIDENT [PRODUCTOS];

DROP TABLE [PRODUCTOS];

RENAME TABLE [PRODUCTOS_Temp] to [PRODUCTOS];

 Just use the IF and if one of them is empty, it says 'Does not exist' else it says 'Yes'.

Jordy

Climber