i have a data table with 2 fields in the table like the below:
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" :
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:
Product, Area, Position
(ooxml, embedded labels, table is Sheet1);
but im getting all nulls in my Position field.
what is the best way to do this?
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...
Looks like a simple "join"(or "left join", if values may be missing in the second file but included in the first) should work here. If the example is exactly as specified, you don't even need the first table, since the second table contains all of the information from the first plus an additional field.
hi @Or thank you for your reply.
you're right, a simple join would work, however i would then need to drop the "Product" table from my second table so they join correctly. doing this then skews my data because the same Area number for one product may have a different Position value.
so if i just joined them, area 1 would = Position 1A for all my product, whereby i may have a Product that Area 1 would = Position 1F for example.
i need to keep the first data table as as you said some values my be missing in the second file but included in the first.
is there a way to get it working correctly?
sorry @Or it was a poor explanation.
essentially my table would look like this
so the 2 products can have the same Area value but different Positions for that value.
using left join would not take this into account
i need to be able have the correct Areas for each Product grouped into the correct Position
In this example, no product has two areas with different positions for that area..? Each combination of Product-Area appears to have exactly one line with exactly one position?
so this is the excel file im pulling in and i have filtered it to show just one "Position" 1A:
you can see that "1A" has different areas for each product.
in my script i then just simply pull in this file and comment our the Product field so i get no synthetic table:
but when i filter the data in the app for Position "1A" and area "1", im getting more products than the 5 that have that area value in my excel file:
what i need is for the app to only show me the 5 products and not every single product in my data
Why are you removing the Product field if this is part of your key? If you just load Area without Product, you'll get all matching areas regardless of product, which doesn't appear to be your desired output...
i have to keep the Product field in my original table as i have some mappings and rules around it which would impact the data in the front end.
so i cant remove the Product field in my original table, hence the issue i am facing.
is there a way to get it working correctly while keeping the Product and Area field in my original table?