Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am new to Qlik and am still learning, so please bear with me.
I have a table that looks like this:
Customer ID | Customer Type | Parent ID | Parent Name | Parent Type |
1 | Dealer | 6 | NE | Region |
1 | Dealer | 7 | US Canada | Area |
1 | Dealer | 8 | NE-1 | District |
2 | Dealer | 6 | NE | Region |
2 | Dealer | 7 | US Canada | Area |
2 | Dealer | 8 | NE-1 | District |
I am trying to get to a table that looks like this:
Customer ID | Area | Region | District |
1 | US Canada | NE | NE-1 |
2 | US Canada | NE | NE-1 |
But when I'm creating dimensions, it ends up like this:
Customer ID | Area | Region | District |
1 | US Canada | - | - |
1 | - | NE | - |
1 | - | - | NE-1 |
2 | US Canada | - | - |
2 | - | NE | - |
2 | - | - | NE-1 |
An example of the expression I have for the dimension is this:
for District:
=if([Customer Type]='Dealer' and [Parent Type]='District',[Parent Name])
What are some things I can do to create a dimension that gives me a table like the one in the middle?
TIA!
The solution I came up with was to add a table in that was "wide", as opposed to "tall". The table looks something like this:
Dealer | District | Region | Area |
1 | NE-1S | NE-S | US Canada |
1 | NE-1P | NE-P | US Canada |
2 | NE-1S | NE-S | US Canada |
2 | NE-1P | NE-P | US Canada |
This way there is a relationship built within the hierarchy that will give intended results. As mentioned previously, this is a new tool for me (I work with Power BI primarily), so still a lot of work, and I appreciate everyone's help, what was shared will definitely be utilized at some point!
You can try the following expression in a measure:
For District:
MaxString({<[Customer Type]={"Dealer"},[Parent Type]={"District"}>}[Parent Name])
It works for me.
Hello, I am sending you a code that works without using the "Parent ID" field.
Now if you add the "Parent_id" field, it will never work because that field is of a lower hierarchy and it will never allow it, for it to work you must remove the "Parent ID" field.
If it is mandatory to use the "Parent ID" field, play around with the "Peek()" command.
Here an example:
datatmp2:
Load *,
If(Len(Area)>0,Area,Peek(Area)) as Area2
Resident DataTmp1;
DropTableDataTmp1;
Regarts.
Thank you @joaopaulo_delco.
This solution works, except that I missed some key information in my rush to get the question out. Many of our Customers are tied to multiple districts. The solution you provide works well for one district, however, if there are multiple, it only shows the first one. To add more complexity, there is more than one region.
Customer ID | Customer Type | Parent ID | Parent Name | Parent Type |
1 | Dealer | 6 | NE-S | Region |
1 | Dealer | 9 | NE-P | Region |
1 | Dealer | 7 | US Canada | Area |
1 | Dealer | 8 | NE-1S | District |
1 | Dealer | 10 | NE-1P | District |
2 | Dealer | 6 | NE-S | Region |
2 | Dealer | 7 | US Canada | Area |
2 | Dealer | 8 | NE-1S | District |
2 | Dealer | 9 | NE-P | Region |
2 | Dealer | 10 | NE-1P | District |
I did find the name of someone in my organization that I will be reaching out to. I'm leaning towards something with the data model, I just need to determine what I need to use to not burden the model any more than I need to.
Thank you for the help!
The solution I came up with was to add a table in that was "wide", as opposed to "tall". The table looks something like this:
Dealer | District | Region | Area |
1 | NE-1S | NE-S | US Canada |
1 | NE-1P | NE-P | US Canada |
2 | NE-1S | NE-S | US Canada |
2 | NE-1P | NE-P | US Canada |
This way there is a relationship built within the hierarchy that will give intended results. As mentioned previously, this is a new tool for me (I work with Power BI primarily), so still a lot of work, and I appreciate everyone's help, what was shared will definitely be utilized at some point!