Merging 2 Column Values

[apeking]

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!

[premvihari]

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 · Accepted solution]

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

[Vegar]

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.

[apeking]

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!