Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
david145
Contributor II
Contributor II

how to create new grouping field

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

Labels (2)
1 Solution

Accepted Solutions
Or
MVP
MVP

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...

View solution in original post

12 Replies
Or
MVP
MVP

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.

JohnSamuel123
Creator
Creator

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?

thanks 

Or
MVP
MVP

I'm sorry, but I wasn't able to follow that explanation... but if you're looking for an action similar to left join that doesn't impact the original table, try Left Keep.

JohnSamuel123
Creator
Creator

sorry @Or it was a poor explanation.

essentially my table would look like this

Product Area Position
Table 1 1A
Table 3 1A
Chair 3 1F
Chair 1 2B
     

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 

sidhiq91
Specialist II
Specialist II

@JohnSamuel123  not sure if I understand your requirement exactly. But a simple join is doing the business for us.

So if you could be more specific with your requirement we can help you out.

Or
MVP
MVP

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?

JohnSamuel123
Creator
Creator

hi @sidhiq91  @Or ,

so this is the excel file im pulling in and i have filtered it to show just one "Position" 1A: 

JohnSamuel123_1-1656338662695.png

 

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:

JohnSamuel123_2-1656338773912.png

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:

JohnSamuel123_3-1656338956726.pngJohnSamuel123_4-1656338989625.png

what i need is for the app to only show me the 5 products and not every single product in my data

thanks

Or
MVP
MVP

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...

JohnSamuel123
Creator
Creator

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?