Advanced crosstable

[#1]

Hello,

I have an excel file in such format:

PROFILE	REDUCTIONFIELD	REDUCTIONVALUE
P02	%REPORTINGUNITCODE	DE001_LC01
P02	%REPORTINGUNITCODE	DE001_LC02
P03	%REPORTINGUNITCODE	DE001_IC01
P04	%REPORTINGUNITCODE	DE001_LC01
P04	%REPORTINGUNITCODE	DE001_LC02
P05	%REPORTINGUNITCODE	DE001_LC01
P05	%REPORTINGUNITCODE	DE001_LC02
P09	%REPORTINGUNITCODE	DE001_LC01
P04	%CUSTOMERDISTRIBUTIONCHANNEL1CODE	001
P05	%CUSTOMERDISTRIBUTIONCHANNEL1CODE	004
P09	%CUSTOMERDISTRIBUTIONCHANNEL1CODE	003
P09	%CUSTOMERDISTRIBUTIONCHANNEL1CODE	004

From this, I would need to create 2 tables:

%REPORTINGUNITCODE:

PROFILE	%REPORTINGUNITCODE
P02	DE001_LC01
P02	DE001_LC02
P03	DE001_IC01
P04	DE001_LC01
P04	DE001_LC02
P05	DE001_LC01
P05	DE001_LC02
P09	DE001_LC01

and %CUSTOMERDISTRIBUTIONCHANNEL1CODE

PROFILE	%CUSTOMERDISTRIBUTIONCHANNEL1CODE
P04	001
P05	004
P09	003
P09	004

It must be a generic routine, so when extra REDUCTIONFIELD are added to the 1st table; extra table would be automatically generated.

I guess, there is a matter of sorting the records at the first place, and identify when the current records is having a new REDUCTIONFIELD.

How would you write such routine in QV script?

Thanks in advance

[lironbaram · Accepted solution]

hei

attach is an example of what you asked for

hope its helps you

[#3]

Exactly what I was looking for! Thanks!