Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Using ApplyMap with Conditional If statement

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


1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

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

View solution in original post

8 Replies
jwjackso
Specialist III
Specialist III

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
Not applicable
Author

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

Regards.

Chris

shiveshsingh
Master
Master

Like this?

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

Anonymous
Not applicable
Author

Thanks Shivesh

Regards,

Chris

Anonymous
Not applicable
Author

HI Jerry

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

Double Counting.JPG

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

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
Not applicable
Author

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
Not applicable
Author

Thanks Manish for your assistance.

Regards

Chris