Indirect Set Analysis Question - Again...

[JustinDallas]

Hello Folks,

So I have the following data.  What I want to do is to get the [Order Travel Lane] on the same line as the [Invoice Number].  The "joining" piece of data is that both the Invoice data and the Order data both share the [Order Header Number].  

If the table used the [Order Header Number], then I could use AGGR(...) but since that's not a table dimension, I can't use it.  Below is what I am attempting to get:

And here is my dummy data:

//BEGIN CODE

Fact:
LOAD *,
If([Invoice Number] <> '-', [Invoice Number], Null() ) AS 'Invoice Number 2',
If([Invoice Revenue] <> '-', [Invoice Revenue], Null() ) AS 'Invoice Revenue 2',
If([Order Travel Lane] <> '-', [Order Travel Lane], Null() ) AS 'Order Travel Lane 2'
;
Load * Inline
[
'Invoice Number','Invoice Revenue','Order Header Number','Order Travel Lane',
270000A, 1543.45, 270000,'-'
'-','-',270000, 'Athens, TX-Mansfield, TX'
]
;

DROP FIELDS [Invoice Number],[Invoice Revenue],[Order Travel Lane]
;
RENAME FIELD [Invoice Number 2] TO [Invoice Number]
;
RENAME FIELD [Invoice Revenue 2] TO [Invoice Revenue]
;
RENAME FIELD [Order Travel Lane 2] TO [Order Travel Lane]
;
EXIT Script
;

//END CODE

I feel as if I need to use Indirect Set Analysis but I'm not sure how to make the jump from Invoice Number -> Order Header Number -> Order Travel Lane

Any help is greatly appreciated.

[yogiachilleos]

Howdy JustinDallas,

I am going to assume a lot here but hopefully the assumptions are fairly close. If I were in your shoes I would do the following:

OrderTravelLane:

Load Distinct

     [Order Header Number],

     [Order Travel Lane] as [Order Travel Lane New]

Resident Fact

Where [Order Travel Lane] <> null(); 

Drop field [Order Travel Lane];

Rename field [Order Travel Lane New] to [Order Travel Lane];

You now have a table that has the correct [Order Travel Lane] and it will display correctly in the table.

Hope this helps!

Kind regards,

Yogi Achilleos

[JustinDallas]

Thanks Yogi, 

I was kind of hoping to avoid datamodel work.  While I know that it's far, far easier and even more foolproof, I wanted to accomplish it with Set Analysis because I won't always be able  to maul my datamodel.

[yogiachilleos]

The big issue I see with doing a conditional if or set analysis for your problem is that you have no way of tying the invoice number of 270**** to null except through header. If you do this on the front side you take away Qlik’s greatest asset which is its speed and optimization. 

I would highly recommend going the script route, but again I had to make a lot of assumptions because I don’t know all the variables you do.

For example: you could have multiple ‘Order Travel Lane’ per ‘Order Header’.. the solution I proposed would multiple your data by that count of multiples. 

If it’s a 1 to 1 that solution is perfect and simple.

I understand the fear of touching a data model, but you have to hit pain points to grow and learn. Just take things slow and methodically and always check your work for unintentional results. 

Kind regards,

Yogi Achilleos

[JustinDallas]

@sunny_talwar