Using ApplyMap with Conditional If statement

[Anonymous]

I have a table with a "SupplierName".  The SupplierName can be blank or can have a value.  If it is blank, i use ApplyMap to provide a SupplierName in the QlikView loading statement as follows:

IndiaImportsMapTable:
Mapping LOAD CleanedDescription,
      Supplier
FROM
[..\Data Files\Imports\IndiaImportsMapping.xlsx]
(ooxml, embedded labels, table is IndiaImportsKnowledgeBase);

Later on in my LOAD script:

ApplyMap('IndiaImportsMapTable', Description) as SupplierName,

How can i use an IF statement to use ApplyMap only when the source field (SQL Server)is empty (isNull) but otherwise use the field name when it exists from the the data source in SQL Server?

Regards.

Chris

[jwjackso · Accepted solution]

I believer you need to reverse your mapping table.

IndiaImportsMapTable:
Mapping LOAD Supplier,

        CleanedDescription,
FROM
[..\Data Files\Imports\IndiaImportsMapping.xlsx]
(ooxml, embedded labels, table is IndiaImportsKnowledgeBase);

If SupplierName is really null and not spaces:

If(IsNull(SupplierName),ApplyMap('IndiaImportsMapTable',Supplier),SupplierName) as SupplierName

If it is spaces:

If(Len(Trim(SupplierName)) < 1),ApplyMap('IndiaImportsMapTable',Supplier),SupplierName) as SupplierName

[Anonymous]

Thanks Jerry, let me run it and will advise outcome.

Regards.

Chris

[shiveshsingh]

Like this?

if(isnull(source_field), ApplyMap('IndiaImportsMapTable', Description),Field_Name) as SupplierName

[Anonymous]

Thanks Shivesh

Regards,

Chris

[Anonymous]

HI Jerry

There is an issue with the above approach, there is double counting of Supplier names as follows:

This means the same Supplier name is coming from the ApplyMap and the other through the load script from SQL Server.  Is there a way of amalgamating the two same supplier names?

Regards.

Chris

[MK_QSL]

Something like this...

IndiaImportsMapTable:
Mapping Load
 Supplier,
 CleanedDescription
Inline
[
 Supplier, CleanedDescription
 A, AAA
 B, BBB
 C, CCC
 D, DDD
 E, EEE
];

Fact:
Load
 Supplier,
 If(IsNull(Description) or Len(Trim(Replace(Description,'-',''))) = 0,
 ApplyMap('IndiaImportsMapTable',Supplier,Null()),
 Description) as Description,
 Sales
Inline
[
 Supplier, Description, Sales
 A, AAA, 100
 B, -, 200
 C,-, 300
 D, DDD, 400
 E, EEE, 500
];

[Anonymous]

I had to use Aggr function to group all the same suppliers as follows:

aggr(sum(ValueUSD), SupplierItemName)

where SupplierItemName is a "Drill Down" Groupfield.

[Anonymous]

Thanks Manish for your assistance.

Regards

Chris