Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apeking
Contributor
Contributor

Merging 2 Column Values

Hi everyone, 

I currently have a table that contains 2 columns that look something like:

Column A   Column B

A                     B

B                     C

D                     D

I'm required to count the total number of items across the 2 columns by the item name, i.e. the table has to look like below:

Item       Count

A              1

B              2

C              1

D              2

I'm relatively new to Qlik Sense, so any help on how to do this would be much appreciated. Thank you for any help you can give!

1 Solution

Accepted Solutions
SergeyMak
Partner
Partner

Let's say your table with Column A and Column B is called Table1.

In that case add the following into your load script

Table2:

LOAD [Column A] AS [Column C]

RESIDENT Table1;

Concatenate (Table2)

LOAD [Column B] AS [Column C]

RESIDENT Table1;

 

Then you can created a frontend Table

Dimension is [Column C]

Expressions is Count([Column C])

 

Hope it helps

Regards,
Sergey

View solution in original post

4 Replies
premvihari
Partner
Partner

Hi,
Please Try below code . All i tried was load Column A first then load Column B as A so that both will concatenate then in front end table chart show ColumnA , count( columnA)

Test:
Load * Inline
"1
A
B
C";

Load * Inline
"1
B
C
D";




SergeyMak
Partner
Partner

Let's say your table with Column A and Column B is called Table1.

In that case add the following into your load script

Table2:

LOAD [Column A] AS [Column C]

RESIDENT Table1;

Concatenate (Table2)

LOAD [Column B] AS [Column C]

RESIDENT Table1;

 

Then you can created a frontend Table

Dimension is [Column C]

Expressions is Count([Column C])

 

Hope it helps

Regards,
Sergey

View solution in original post

Vegar
Partner
Partner

If I assume that you want this calculation to be attached to the rest of your application I suggest adding a new field in your original table and adding a new table based on that new fiekd value. I'll explain.

OriginalPlusField:

Load 

*,

[Column A] & '|' & [Column B] as [%ColumnKeyAB]

Resident Original;

Drop table Original;

 

[AB table]:

Load 

%ColumnKeyAB,

Subfield(%ColumnKeyAB,'|') as [Column AB]

Resident OriginalPlusField;

 

Then use Column AB as your dimension for your calculation.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
apeking
Contributor
Contributor

Hi everyone!

Thank you for your prompt responses - really appreciate the help. I tried your respective solutions and Sergey's one worked best for me. 

Thanks again!