Combine Columns & Rows - Business One OCQG & OCRD

[sprqlikview]

I'm not sure how this could be solved but I'm sure there is a way.

I have the following two tables:

Table: OCQG

GroupCode	GroupName
1	Easter Newsletter
2	Christmas Newsletter
3	Summer Newsletter
4	Valentines Newsletter
5	Winter Newsletter
...	...

Table: OCRD

CustomerCode	Group1	Group2	Group3	Group4	Group5	...
C10050	N	Y	N	N	Y	...
C10051	Y	Y	N	N	N	...
....

I now want to know for each customer which newsletter they want and I imagine to get an end result of:

CustomerCode	GroupCode	GroupName	Newsletter
C10050	1	Easter Newsletter	N
C10050	2	Christmas Newsletter	Y
C10050	3	Summer Newsletter	N
....

Is there a way to combine the rows and columns?

Thank you

[Gysbert_Wassenaar]

TempOCRD:

CrossTable(GroupCode,Newsletter)

LOAD * FROM ....;

OCRD:

Noconcatenate

LOAD CustomerCode,

          keepchar(GroupCode, '0123456789') as GroupCode,

          Newsletter

RESIDENT TempOCRD;

DROP TABLE TempORCD;

[jonathandienst]

Adding the newsletter names as well:

MapNames:

Mapping LOAD GroupCode,

  GroupName

FROM OCQG;

TempOCRD:

CrossTable(GroupCode,Newsletter)

LOAD * FROM ....;

OCRD:

Noconcatenate

LOAD CustomerCode,

  keepchar(GroupCode, '0123456789') as GroupCode,

  ApplyMap('MapNames', keepchar(GroupCode, '0123456789')) As GroupName,

  Newsletter

RESIDENT TempOCRD;

DROP TABLE TempORCD;