Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor II

Combine Columns & Rows - Business One OCQG & OCRD

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

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

Table: OCRD

CustomerCodeGroup1Group2Group3Group4Group5...
C10050NYNNY...
C10051YYNNN...
....

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

CustomerCodeGroupCodeGroupNameNewsletter
C100501Easter NewsletterN
C100502Christmas NewsletterY
C100503Summer NewsletterN
....

Is there a way to combine the rows and columns?

Thank you

Tags (1)
2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Combine Columns & Rows - Business One OCQG & OCRD

TempOCRD:

CrossTable(GroupCode,Newsletter)

LOAD * FROM ....;


OCRD:

Noconcatenate

LOAD CustomerCode,

          keepchar(GroupCode, '0123456789') as GroupCode,

          Newsletter

RESIDENT TempOCRD;


DROP TABLE TempORCD;


talk is cheap, supply exceeds demand
MVP
MVP

Re: Combine Columns & Rows - Business One OCQG & OCRD

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein