Pivot non-numeric data?

Kinetic ERP
1

This is a BAQ question, but I will illustrate in Excel.

I have this data - job numbers and some serial-tracked part numbers that were issued to those jobs. I categorized them into 12 groups, like:

  • Engine
  • Transmission
  • Front axle
  • Rear axle

I want a result with those categories as column headers and a single job number per row, with the part number of the component in each category column.

Like so:

image

In my mind, I have “pivot-ed” the data, but a “pivot” - in SQL and Excel - is an aggregation of numeric data, or a count of non-numeric data, but that’s it - the result is always numeric, not some field from a row.

Right? Or could this be done in a BAQ? The only way I know would be death by 12 subqueries.

In Excel, I hacked it like this. I numbered every row, then pivoted the “sum” of those row numbers, but it’s 1:1, so I end up with an index that I can VLOOKUP off of.

Anyone have a better way (in Epicor)?

image
image1030×387 21.8 KB

Sheet1:

image
image751×599 40.1 KB

2

This is the way. Yes, it sucks to make a separate subquery for each one, but if you know you need that many from the beginning you can make it a little cleaner by naming each subquery for the category it will return results for. That makes the visual joins a little easier to read. Then just join it all up on the top level by job number. It looks like you are not trying to return serial numbers just the part numbers, so these subqueries should be pretty simple. Post up your BAQ when you get close and we can help fine tune it.

If there really is a way to do this without a subquery for each category, I would love to learn it too!

3

Are the parts identifiable in Epicor? Like the part class is engine or something?

4

Resisting the urge to put a GIF here…

I already started down the 12-subquery path. I’m up to 6! All in all, I think I have the same logic you described.

image
image748×637 42.1 KB

1 Like
5

Not exactly, but I have enough to go on.

image
image623×811 40 KB

Subquery is already filtered to TrackSerialNum = 1

6

You could just do a calculated field for each one. If there is only 1 or none of each on a job.

7

@jkane Right, but that doesn’t get me off the hook for bringing in 12 subqueries, right?

8

Sure, why would it not? If every job has one or none of each category, just do a case statement looking for the description and then return the part number.

1 Like
9

Thats a good point @jkane! If you know you only have one part per job for each class then this should work.

10

I think you would need a MAX too.

MAX(
Case
When Then
Else 0
End)
11

[Edit - let’s say JobHead instead]

Ok, so let’s pretend the center table is… the JobHead table.

And the other is essentially JobMtl (joined to Part, for the TrackSerialNum field).

How would I join “JobHead” to the JobMtl subquery?

12

Just load the three tables. JobHead as 1, JobMtl as 2, and Part as 3. Join them and you are good to go. 1 Query.

13

But I’d get many rows for one job, right?

14

I don’t know. I am just thinking of the Job Number (which will be grouped) and the columns for the part category (which will have the MAX). Is there other data in the query?

15

Just try it with JobNum and Engine only. See what happens. If it works like you want, we can continue to extrapolate the rest of the query.

16

Ahh, interesting! I can see this. That just might work.

17

@jkane Brilliant. Runs in half the time and was much easier to produce.

image
image1277×196 5.85 KB

18

Excellent! Glad I could help.

1 Like
19
20

Well I don’t know why you linked to that but I did somehow miss that whole (55-post) thread, so that was, um, worth it?

Anyway, you liked the sunburst? (Which is now abandoned.)

Or is it a flowerbox?

image

1 Like