Customer Hierarchy Mapping

[robenanderson]

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!

[joaopaulo_delco]

Hi @robenanderson 

          You can try the following expression in a measure:

For District:

MaxString({<[Customer Type]={"Dealer"},[Parent Type]={"District"}>}[Parent Name])

It works for me.

[cristianj23a]

Hello, I am sending you a code that works without using the "Parent ID" field.

Data:

LOAD

    "Customer ID",

    "Customer Type",

//     "Parent ID",

    "Parent Name",

    "Parent Type"

FROM [lib://DataFiles/TestCrossTable.xlsx]

(ooxml, embedded labels, table is Hoja2);

DataTmp1:

Load 

"Customer ID",

     "Customer Type",

//     "Parent ID",

     "Parent Name" AS Area

//      "Parent Type" AS Area

Resident Data Where "Parent Type"='Area';

Outer Join(DataTmp1)

Load 

"Customer ID",

     "Customer Type",

//     "Parent ID",

     "Parent Name" AS Region

//      "Parent Type" AS Area

Resident Data Where "Parent Type"='Region';

Outer Join(DataTmp1)

Load 

"Customer ID",

     "Customer Type",

//     "Parent ID",

     "Parent Name" AS District

//      "Parent Type" AS Area

Resident Data Where "Parent Type"='District';

Drop Table Data;

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.

[robenanderson]

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!

[robenanderson · Accepted solution]

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!