Implementing Crosstable without using cross table for learning purposes

[#1]

Hi All,

I'm trying to learn the structure of coding in the script environment. So i'm trying to implement crosstable functionality without using the crosstable function.

Has anyone got an ideas as to how I should go about this.

Initial Data layout would be like so

Year	ProdID	Test1	Test2	Test3
2003	A	45	12	92
2004	B	62	78	11
2005	A	76	89	64

Resultant Data Layout

Year	ProdID	TestCase	Result
2003	A	Test1	45
2003	A	Test2	12
2003	A	Test3	92
2004	B	Test1	62
2004	B	Test2	78
2004	B	Test3	11
2005	A	Test1	76
2005	A	Test2	89
2005	A	Test3	64

Any help would be greatly appreciated.

Thanks, Ken

[#2]

Hi Ken,

Your Initial data and result the same..

Maybe a mistake?

Regards,

Lester

[#3]

Thanks Iprosete.

Ken

[Sokkorn · Accepted solution]

Hi Ken,

Try this script:

1. If you want to user CrossTable

================================

[Data]:

LOAD * Inline [

Year,    ProdID,    Test1,    Test2,    Test3

2003,    A,        45,        12,        92

2004,    B,        62,        78,        11

2005,    A,        76,        89,        64];

[Data2]:

CrossTable(Year,Value2,2)

LOAD

    Year    AS Years,

    ProdID,

    Test1,

    Test2,

    Test3

Resident [Data];

RENAME Field [Year] to [Value1];

DROP Table [Data];

================================

2. If you don't want to use Cross Table

================================

[Data]:

LOAD * Inline [

Year,    ProdID,    Test1,    Test2,    Test3

2003,    A,        45,        12,        92

2004,    B,        62,        78,        11

2005,    A,        76,        89,        64];

[Data1]:

LOAD

    Year    AS NewYear,

    ProdID    AS NewProdID,

    'Test1'    AS Fields,

    Test1    AS Value

Resident [Data];

[Data2]:

Concatenate ([Data1])

LOAD

    Year    AS NewYear,

    ProdID    AS NewProdID,

    'Test2'    AS Fields,

    Test2    AS Value

Resident [Data];

[Data2]:

Concatenate ([Data1])

LOAD

    Year    AS NewYear,

    ProdID    AS NewProdID,

    'Test3'    AS Fields,

    Test3    AS Value

Resident [Data];

DROP Table [Data];

================================

For me I prefer Cross Table than second solution.

Regards,

Sokkorn

[#5]

Hi Ken,

Here's what I did:

Initial:
LOAD * INLINE [
    Year, ProdID, Test1, Test2, Test3
    2003, A, 45, 12, 92
    2004, B, 62, 78, 11
    2005, A, 76, 89, 64
];

Result:
Load Year,
  ProdID,
  'Test1' as TestCase,
  Test1 as Result
Resident Initial;

concatenate
Load Year,
  ProdID,
  'Test2' as TestCase,
  Test2 as Result
Resident Initial;

concatenate
Load Year,
  ProdID,
  'Test3' as TestCase,
  Test3 as Result
Resident Initial;

drop table Initial;

Regards,

Lester

[#6]

I'm more interested in creating the crosstable function (and i didn't explicitly state this) but you've both answered my question.

Thanks very much.

Ken