topic Re: Using ApplyMap with Conditional If statement in QlikView

Using ApplyMap with Conditional If statement

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Using ApplyMap with Conditional If statement

jwjackso — Sun, 11 Mar 2018 02:07:54 GMT

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

Re: Using ApplyMap with Conditional If statement

Anonymous — Sun, 11 Mar 2018 02:19:25 GMT

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

Regards.

Chris

Re: Using ApplyMap with Conditional If statement

shiveshsingh — Sun, 11 Mar 2018 02:29:55 GMT

Like this?

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

Re: Using ApplyMap with Conditional If statement

Anonymous — Sun, 11 Mar 2018 04:05:13 GMT

Thanks Shivesh

Regards,

Chris

Re: Using ApplyMap with Conditional If statement

Anonymous — Sun, 11 Mar 2018 07:40:02 GMT

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

Re: Using ApplyMap with Conditional If statement

MK_QSL — Sun, 11 Mar 2018 08:09:55 GMT

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
];

Re: Using ApplyMap with Conditional If statement

Anonymous — Sun, 11 Mar 2018 08:46:28 GMT

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.

Re: Using ApplyMap with Conditional If statement

Anonymous — Sun, 11 Mar 2018 11:20:08 GMT

Thanks Manish for your assistance.

Regards

Chris