topic Suggestions to Generate Comparision Data in QlikView

Suggestions to Generate Comparision Data

pvragel — Tue, 23 Feb 2010 19:17:58 GMT

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.

Suggestions to Generate Comparision Data

rwunderlich — Tue, 23 Feb 2010 19:42:37 GMT

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

Suggestions to Generate Comparision Data

tseebach — Tue, 23 Feb 2010 19:51:20 GMT

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 🙂

Suggestions to Generate Comparision Data

pvragel — Tue, 23 Feb 2010 20:56:14 GMT

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

Suggestions to Generate Comparision Data

pvragel — Tue, 23 Feb 2010 20:58:04 GMT

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

Appreciate your help.

Regards,

Paul

Suggestions to Generate Comparision Data

rwunderlich — Tue, 23 Feb 2010 21:48:15 GMT


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

Suggestions to Generate Comparision Data

pvragel — Wed, 24 Feb 2010 00:55:21 GMT

Thanks Rob.

Paul