topic AW:Help with TOPN data loading script in QlikView

Help with TOPN data loading script

Tue, 01 Sep 2009 00:37:25 GMT

I have a text file (very large) with names of people and actions they performed...

Greg, ate_food
Bill, ate_food
Greg, no_show
Bill, lost_weight
Cindy, weigh_in
...

Loading in the data is easy but is there a way to get it into a table with columns that look like this during the data load?

Name, Action1, Action2, Action3, Count1, Count2, Count3
Greg, ate_food, weigh_in, no_show, 1548, 477, 23
Bill, ate_food, no_show, lost_weight, 2101, 332, 321
...

I know I need to look at all the data at least once before I know what the top 3 actions each person performed but other than that I'm not sure if this is possible or how to efficiently go about loading a table with the top three actions for each person.

Help with TOPN data loading script

disqr_rm — Tue, 01 Sep 2009 02:57:13 GMT

I think you would need o use "Generic LOAD" statement. I didn't spend much time but something like:

First:
LOAD * INLINE [
Name, Action
Greg, ate_food
Bill, ate_food
Greg, no_show
Bill, lost_weight
Cindy, weigh_in
];

Final:
Generic Load *, 1 as dummy resident First;

drop table First;

Just an idea.

AW:Help with TOPN data loading script

biester — Tue, 01 Sep 2009 08:45:12 GMT

Hi,

find my suggestion and trials in enclosed qvw. Don't know if it's the most elegant way, but it seems to work. Perhaps this could be helpful.

Rgds,
Joachim

AW:Help with TOPN data loading script

Wed, 02 Sep 2009 02:07:28 GMT

very nice work - i figured i would have to do at least one temp table load to perform the aggregation and then another load for building a table with only 1 row per person. i can worry about doing it in less loading steps later but from here i should at least be able to write it out to qvd so the work only needs to be done the first time when reading in new log files.

thanks again - 2 thumbs up