Suggestions to Generate Comparision Data

[pvragel]

I have an application that takes all material in a certain length range, segregates it by product type, does a calculation on the length, and summarizes the resulting weight. (It uses 2 joined files, and 2 cross-table loads which are not joined). Works fine.

I now need to compare that information to information from 4 other sources (Excel) each of which also has length and product type, and end up with a table with all 5 weight values, within each length range and product type, and the ability to slide/dice the data across all the original dimensions.

I tried a "Group by" to individually calculate the data I need, but generated 10 synthetic tables and bombed the application.

I'd appreciate suggestions on how to generate the comparison, and be able to use a single set of list boxes (weight, size, date, location…) to view the comparison across all 5.

Thanks in advance for suggestions.

[rwunderlich · Accepted solution]

If I understand correctly, one appoach would be to create a single concatenated table. The only difference between rows would be the weight value being assigned to a unique column. Something like this:

Facts:
LOAD Product, Length, blah*blah AS Weight1
FROM source1.xls
;
CONCATENATE (Facts)
LOAD Product, Length, blah*blah AS Weight2
FROM source2.xls
;

etc

-Rob

[tseebach]

When I've done comparisons, then I've put all needed data into one fat fact table (like Robs). And then since 8.5 used bookmarks and set analysis to do the comparisons.

You would set up your comparison charts so its easy to select the different data slices. And when you have selected the first series, and press a bookmark bottom (you can easily create a new bookmark BM01 with an action, and then overwrite it each time the button is pressed).

Now let the user do a new selection, the one to compare against.

And since you simply added {BM01} to all your aggregations functions. Then Sum(Value) would compare to the bookmark created BM01 using Sum({BM01} Value). All your charts would show comparisons of your data.

No need to do heavy precalculations 🙂

[pvragel]

Thanks Rob - simple and works.

Is there a way to also segregate the outputs by the original input field? I could create a separate small file for each with a name and join it.

Is it possible to generate a field with the file name used in the script?

thanks for your help.

Paul

[pvragel]

Thanks Seebach. I'll keep that option in mind as well.

Appreciate your help.

Regards,

Paul

[rwunderlich]

pvragel wrote:
Is it possible to generate a field with the file name used in the script?

LOAD filename() as fname
FROM xxx.xls

-Rob

[pvragel]

Thanks Rob.

Paul