Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor II

Add new columns with content linked to existing columns

Hello,

I am pretty new at Qlik Sense and have a question where you might be able to support.

I loaded an excel file in Qlik Sense with several columns: ColumnA/ColumnB/ColumnC/ColumnD/ColumnE/ColumnF/ColumnG

 

I want to add three columns to the table above:

 

ColumnH:

 

if ColumnA IS empty, ColumnH is "Value1"

If ColumnA IS NOT empty AND ColumnB = "N", ColumnH is "Value2"

If ColumnA IS NOT empty AND ColumnB = "Y" AND ColumnC IS empty, ColumnH is "Value3"

If ColumnA IS NOT empty AND ColumnB = "Y" AND ColumnC ="2", ColumnH is "Value4"

If ColumnA IS NOT empty AND ColumnB = "Y" AND ColumnC ="3", ColumnH is "Value5"

If ColumnA IS NOT empty AND ColumnB = "Y" AND ColumnC ="4", ColumnH is "Value6"

 

ColumnI:

 

If ColumnB = "Y" AND ColumnD IS empty, ColumnI is "Value7"

If ColumnB = "Y" AND ColumnD contains "Root", ColumnI is "Value8"

If ColumnB = "Y" AND ColumnD NOT contains "Root", ColumnI is "Value9"

 

ColumnJ:

 

ColumnJ = ColumnE + "_" + ColumnF + "_" + ColumnG

 

May I ask how would it work in the Data load editor?

Thank you in advance.

Best regards,
Rubén

2 Solutions

Accepted Solutions
Highlighted
Valued Contributor

Re: Add new columns with content linked to existing columns

Hi @RubenMoreno 

You can add the part in red to your load statement:

LOAD
ColumnA,
ColumnB,
ColumnC,
ColumnD,
ColumnE,
ColumnF,
ColumnG,
If(Len(Trim(ColumnA))=0,'Value1',If(ColumnB='N','Value2',If(Len(Trim(ColumnC))=0,'Value3',Pick(ColumnC-1,'Value4','Value5','Value6')))) AS ColumnH,
If(ColumnB = 'Y',If(Len(Trim(ColumnD))=0,'Value7',If(ColumnD='Root','Value8','Value9'))) AS ColumnI,
ColumnE&'_'&ColumnF&'_'&ColumnG AS ColumnJ
From
...

If you want to you can have a look at this article for more info on Pick and Match Functions.

Hope this helps.

Mauritz

View solution in original post

Highlighted
Valued Contributor

Re: Add new columns with content linked to existing columns

Hi @RubenMoreno 

Sorry, I misunderstood your requirement. You can use SubStringCount.

If(ColumnB = 'Y',If(Len(Trim(ColumnD))=0,'Value7',If(SubStringCount(ColumnD,'Root')>0,'Value8','Value9'))) AS ColumnI

Let me know if you still have problems.

Regards,

Mauritz

View solution in original post

3 Replies
Highlighted
Valued Contributor

Re: Add new columns with content linked to existing columns

Hi @RubenMoreno 

You can add the part in red to your load statement:

LOAD
ColumnA,
ColumnB,
ColumnC,
ColumnD,
ColumnE,
ColumnF,
ColumnG,
If(Len(Trim(ColumnA))=0,'Value1',If(ColumnB='N','Value2',If(Len(Trim(ColumnC))=0,'Value3',Pick(ColumnC-1,'Value4','Value5','Value6')))) AS ColumnH,
If(ColumnB = 'Y',If(Len(Trim(ColumnD))=0,'Value7',If(ColumnD='Root','Value8','Value9'))) AS ColumnI,
ColumnE&'_'&ColumnF&'_'&ColumnG AS ColumnJ
From
...

If you want to you can have a look at this article for more info on Pick and Match Functions.

Hope this helps.

Mauritz

View solution in original post

Highlighted
New Contributor II

Re: Add new columns with content linked to existing columns

Hello @Mauritz_SA ,

 

It works almost perfect but I still have one mistake.

In the following formula

If(ColumnB = 'Y',If(Len(Trim(ColumnD))=0,'Value7',If(ColumnD='Root','Value8','Value9'))) AS ColumnI,

Column D contains (or not) the word "Root" but it not alone. It means, the content of the cell could be "Root cause: Supplier" or "Root cause: Manufacturing".  At the moment, with the formula above, it displays in ColumnI either Value7 or Value9 but never Value8. Can you maybe support here? I tried putting *Root* and it is unfortunately not working.

 

Thank you.

 

Best regards,
Rubén

Highlighted
Valued Contributor

Re: Add new columns with content linked to existing columns

Hi @RubenMoreno 

Sorry, I misunderstood your requirement. You can use SubStringCount.

If(ColumnB = 'Y',If(Len(Trim(ColumnD))=0,'Value7',If(SubStringCount(ColumnD,'Root')>0,'Value8','Value9'))) AS ColumnI

Let me know if you still have problems.

Regards,

Mauritz

View solution in original post