Help with TOPN data loading script

[#1]

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.

[disqr_rm]

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.

[biester · Accepted solution]

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

[#4]

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