Concatenate two tables and keep duplicates

[wanyunyang]

Hi guys,

I have two tables like:

Select Customer, 1 as Score resident table1;

concatenate

Select Customer, 1 as Score resident table2;

And sum(Score) for each customer returns 2. It seems duplicates are removed. I wonder if there is a way to keep them.

Thanks in advance!

[Vegar · Accepted solution]

The rows are not removed. They are still there, but Qlik never presents duplicate rows in a table. 

You can verify that all rows are kept by looking into the table in the data model overview. 9 or creating an table with customer as a dimension and count(customer) as a measure. You will see that you have two records per customer (if customer is present in both source tables. 

To display all customer rows separately You need some kind of row identifier to your final table.

Try this.

Select rowno() as RowId, Customer, 1 as Score resident table1;

concatenate

Select rowno() as RowId, Customer, 1 as Score resident table2;