HELP: Total of up level, grouping by "part of its sub level" (hard to explain, I'll try)

[#1]

(From Brazil):

Hi everyone, this is my first participation here, I believe I had nothing to share while I am in "baby" level.

I know, I need to increase my level and I do pretend be part of this rich community.

Well, I'm in big trouble with my doubt below, can anyone help me?

I got 2 tables, and I need to catch the "m_Product" from tab1 and find its value(group) in tab2.

But the main problem is, in the tab2 I have the full path of the product while in the tab 1, I have the up level.

How can I do that? Any idea will be welcome 😉

tab1:

LOAD * INLINE [

m_Product, m_Path, m_Month, m_Year

PG080A, 0020, 6, 2016

PG080A, 0020.0100, 6, 2016

PG080A, 0020.0110, 6, 2016

PG080A, 0020.0198, 6, 2016

PL070A, 0020, 6, 2016];

//

tab2:

LOAD * INLINE [

d_Product, d_Path, d_Month, d_Year, d_Value

PG080A,0020.0100.0190, 6, 2016, 150

PG080A,0020.0100.0190, 6, 2016, 180

PG080A,0020.0110.0230, 6, 2016, 80

PG080A,0020.0110.0200, 6, 2016, 110

PG080A,0020.0198.9000, 6, 2016, 60,

PL070A, 0020.0198.9000, 6, 2016, 45 ];

The desired result, in the PIVOT TABEL, would be:

===> PG080A-0020-6-2016- 580

==>PG080A - 0020.0100 - 6 - 2016 - 330

PG080A,0020.0100.0190, 6, 2016, 180

PG080A,0020.0100.0190, 6, 2016, 150

==>PG080A - 0020.0110 - 6 - 2016 - 190

PG080A,0020.0110.0230, 6, 2016, 80

PG080A,0020.0110.0200, 6, 2016, 110

==>PG080A - 0020.0198 - 6 - 2016 - 60

PG080A,0020.0198.9000, 6, 2016, 60,

===> PL070A-0020-6-2016- 45

==>PL070A - 0020.0198 - 6 - 2016 - 45

PL070A - 0020.0198.9000 - 6 - 2016 - 45

That's it. Well, I tried to do it in so many different ways, you guys, is my last bet to can solve it.

Thaks a lot.

[antoniotiman · Accepted solution]

Hi Adriano,

see Attachment.

Regards,

Antonio

[#3]

Thank you so much Antonio.

It was exactly what I looking for. And, I have 100% of sure that you has help so muching people than me with this solution.

Feel free to ask me anything when you need.

Have a nice weekend.

😉