how to create new grouping field

[david145]

hi,

i have a data table with 2 fields in the table like the below:

Product	Area
Table	1
Table	3
Table	5
Chair	3
Chair	7
Sink	1
Sink	5
Door	1
etc	etc

each product can have repeating areas as shown above.

i now want to include a new grouping field in my dataset to group certain Areas for each Product. i need to pull in a excel file with the new field named "Position" :

Product	Area	Position
Table	1	1A
Table	3	1A
Table	5	1F
Chair	3	1A
Chair	7	2B
Sink	1	1A
Sink	5	2B
etc	etc	etc

i want to be able to be able to see what "areas" are in what "Position" for each of my "Products".

Ive tried doing a simple Concatenation on my original dataset with my new excel file:

Concatenate(MyData)
load
Product, Area, Position

FROM
[..\..\..\.xlsx]
(ooxml, embedded labels, table is Sheet1);

but im getting all nulls in my Position field.

what is the best way to do this?

thanks

[Or]

Why would you have to remove it? There's no problem with having the same field name in multiple tables when that field is a key... in fact, that's how Qlik is meant to work.

[JohnSamuel123]

because if i keep it in both tables, then the tables will have 2 fields with the same name and this will cause an synthetic issue i thought?

e.g:

i thought i could only have one field in each table with the same values, similar to a join statement?

[Or · Accepted solution]

Synthetic keys are not a problem. They're just Qlik's way of creating one key from multiple fields. If you prefer, you can concatenate the two underlying fields and use that as a key instead of keeping the original fields. However, you can't create a correct join by eliminating one of the key fields entirely...