Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
pvragel
Partner
Partner

Suggestions to Generate Comparision Data

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.

1 Solution

Accepted Solutions
rwunderlich

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

View solution in original post

6 Replies
rwunderlich

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
Partner
Partner

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
Partner
Partner
Author

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
Partner
Partner
Author

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
Partner
Partner
Author

Thanks Rob.

Paul