Conditional Statement in Data Load Editor

[skilloran]

Hello,

I am new to Qlik and have been having some issues figuring this out.

One of the tables I am loading contains 1 field called FactType that the contents need to be broken into two parts for how I need my visualizations to look. Which of the 2 it defaults to depends on another fields contents

I am trying to create an IF statement that says along the lines of: IF field1 contains 'A', then field2 =  'ABC'  and IF field1 contains 'B',  then field2 = 'CBA'

Everything I have been trying keeps resulting in the error of "Field2 Not Found"

Script below:

NodeName as Desk,
floor(BusinessDate) as %CDATE,
Value,
ValueType,
'IR01' as FactType,
 if(ValueType LIKE 'IR01', FactType = 'IR01'), 
 if(ValueType LIKE 'MV', FactType = 'Market Value')
'A' as Table
FROM database
(qvd)
where exists(Desk,NodeName);

[sunny_talwar]

Try this

NodeName as Desk,
floor(BusinessDate) as %CDATE,
Value,
ValueType,
If(ValueType LIKE 'IR01', 'IR01'),
If(ValueType LIKE 'MV', 'Market Value')) as FactType,
'A' as Table
FROM database (qvd)
where exists(Desk,NodeName);

[skilloran]

So this no longer produced the error of Field Name Not Found, however in my Pivot Table that I am using, it doesn't show the two values IR01 and Market Value

For further context, I have 3 files, 2 of them only contain one type of data attribute (what I am calling FactTypes) like CR01 or Vega, the 3rd file however contains two FactTypes, IR01 and Market Value. By loading the 3 files seperately and concatenating the tables, using 'CR01' as FactType in the load statement for the one file, and 'Vega' as FactType in the load statement for the second file, in my pivot table it splits it up like headers for the data points associated with them.

Using the above script no longer associates a name with FactType in the first file.

What I am trying to do is have two separate names applied to the same field based on the contents of the field ValueType.

I attached an image of what the headers for CR01 and Vega look like!

[sunny_talwar]

So, it looks like you need a wildcard search, you can try one of these

If(ValueType LIKE 'IR01*', 'IR01'),
If(ValueType LIKE 'MV*', 'Market Value')) as FactType,

or

If(WildMatch(ValueType, 'IR01*'), 'IR01'),
If(WildMatch(ValueType, 'MV*'), 'Market Value')) as FactType,

Basically use * to for text after MV

[skilloran]

It's now working to show Market Value as a header, but it showing a blank for the header of the 'IR01' value, which I'm not sure why that would be happening. 

Thank you btw for all your help so far!

[sunny_talwar]

In the example you shared, I didn't see any IR01 ValueType. Can you share an example of how it looks?

[skilloran]

My bad, the chart is kind of large so it didn't show the IR01 ValueTypes!

[sunny_talwar]

Looks like it should have worked, but may be because there are some whitespaces before the letter I? Try this may be

If(WildMatch(ValueType, '*IR01*'), 'IR01'),
If(WildMatch(ValueType, 'MV*'), 'Market Value')) as FactType,

Added * before I

[skilloran]

It is still creating the blank header, I have a meeting with another Qlik Developer I work with this afternoon and I will let you know if we come to a solution!

[sunny_talwar]

Sounds good.