topic Re: Implementing Crosstable without using cross table for learning purposes in QlikView

Implementing Crosstable without using cross table for learning purposes

Wed, 05 Jun 2013 07:49:33 GMT

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

Re: Implementing Crosstable without using cross table for learning purposes

Wed, 05 Jun 2013 07:59:12 GMT

Hi Ken,

Your Initial data and result the same..

Maybe a mistake?

Regards,

Lester

Re: Implementing Crosstable without using cross table for learning purposes

Wed, 05 Jun 2013 08:31:26 GMT

Thanks Iprosete.

Ken

Re: Implementing Crosstable without using cross table for learning purposes

Sokkorn — Wed, 05 Jun 2013 09:04:38 GMT

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

Re: Implementing Crosstable without using cross table for learning purposes

Wed, 05 Jun 2013 09:05:18 GMT

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

Re: Implementing Crosstable without using cross table for learning purposes

Wed, 05 Jun 2013 09:38:09 GMT

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