Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
chitemerere
Specialist
Specialist

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

View solution in original post

chitemerere
Specialist
Specialist
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

chitemerere
Specialist
Specialist
Author

Thanks Shivesh

Regards,

Chris

chitemerere
Specialist
Specialist
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

];

chitemerere
Specialist
Specialist
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.

chitemerere
Specialist
Specialist
Author

Thanks Manish for your assistance.

Regards

Chris