Trouble concatenating data to table during loop

[thereal_sandman]

I have a table where multiple "categories" are listed in a column and I want to expand those out so each category is on a single row, but having duplicated all of the other data.  Like this:

Before processing:

[base_tbl]:
LOAD * INLINE [
num, category, statement
1, AA, very good
2, BBB, good
3, CCC, ok
4, AAABBB, bad
5, BBBCCC, very bad
];

After processing:
1, AA, very good
2, BBB, good
3, CCC, ok
4, AAA, bad
4, BBB, bad
5, BBB, very bad
5, CCC, very bad

I am using the script below to extract the single categories (which are only 2 or 3 characters) into a table. I then want to loop through that table and call back to the [base_tbl] to only return the rows containing the substring of interest.

Can't get it to work and hoping the community can help out!

[base_tbl]:
LOAD * INLINE [
num, category, statement
1, AA, very good
2, BBB, good
3, CCC, ok
4, AAABBB, bad
5, BBBCCC, very bad
];

// get all length-2 categories
[loop_control]:
load category
resident [base_tbl] where len(category)=2;

// get all length-3 categories
load category
resident [base_tbl] where len(category)=3;

let num_rows=NoOfRows('loop_control');
for i=0 to $(num_rows)-1

let cat = peek('category',$(i));
trace $(i) - $(wa);

[expanded_data_tbl]:
load *
resident [base_tbl] where category like '*$(cat)*';

next

[rwunderlich · Accepted solution]

I think you can do it without a loop, using Mapping. SubField and Preceding load. Like this:

[base_tbl]:
LOAD * INLINE [
num, category, statement
1, AA, very good
2, BBB, good
3, CCC, ok
4, AABBB, bad
5, BBBCCC, very bad
];

Cat_Map:
Mapping
LOAD category,  chr(1) & category & chr(1)
Resident base_tbl
where Match(len(category), 2, 3)
;

FinalTable:
NoConcatenate
LOAD *
Where len(category)
;
LOAD
  num,
  SubField(mappedcat, chr(1)) as category,
  statement
; 
LOAD
  *,
  MapSubString('Cat_Map', category) as mappedcat
Resident base_tbl
; 

Drop Table base_tbl; 

I assumed the #4 was supposed to have "AA". not "AAA'. 

Join me in Vienna 30 Sept for more Qlik script patterns and solutions. 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[thereal_sandman]

@rwunderlich, not only does your solution work, but you also found my own mistake when creating the test data.  Outstanding solution.  Thank you very much!