Crosstable insideout

Anonymous — Wed, 07 Nov 2012 15:43:28 GMT

Hello community

I want to do the opposite of what the crosstable is doing.

In 'lTolTypRef' there are only three possible values 10 ; 20 ; 30. These values I want to have as columns:

Is it possible??

Cheers chesterluck

lJob	lTolRef	10	20	30
143143	8225	T-WA	-
143143	14461	-	T-WZBAU
143143	17371	-	-	Halle D

Re: Crosstable insideout

mark_casselman — Sun, 10 Nov 2013 17:26:40 GMT

Hi chesterluck,

I was looking for a similar solution on the discussions and found that GENERIC tables are the solution.

Rewritten for your use (i hope there are no typo's):

// Make a generic table for every case

Generic_Tables:

GENERIC LOAD IJob, IToIRef, 'REF_' & IToITypRef, sDepot

RESIDENT L9;

//list all jobs

Jobs:

LOAD Distinct IJob

resident YourTableName;

drop tables YourTableName;

// join all generic tables

FOR i = 0 to NoOfTables()

TableList:

LOAD TableName($(i)) as Tablename AUTOGENERATE 1

WHERE WildMatch(TableName($(i)), 'Generic_Tables.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

LET vTable = FieldValue('Tablename', $(i));

LEFT JOIN (Klanten) LOAD * RESIDENT $(vTable);

DROP TABLE $(vTable);

NEXT i

DROP TABLE TableList;

Hope this works...