Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Implementing Crosstable without using cross table for learning purposes

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

YearProdIDTest1Test2

Test3

2003A451292
2004B627811
2005A768964

Resultant Data Layout

YearProdIDTestCaseResult
2003ATest145
2003ATest212
2003ATest392
2004BTest162
2004BTest278
2004BTest311
2005ATest176
2005ATest289
2005ATest364

Any help would be greatly appreciated.

Thanks, Ken

1 Solution

Accepted Solutions
Sokkorn
Honored Contributor

Re: Implementing Crosstable without using cross table for learning purposes

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 Replies
Not applicable

Re: Implementing Crosstable without using cross table for learning purposes

Hi Ken,

Your Initial data and result the same..

Maybe a mistake?

Regards,

Lester

Not applicable

Re: Implementing Crosstable without using cross table for learning purposes

Thanks Iprosete.

Ken

Sokkorn
Honored Contributor

Re: Implementing Crosstable without using cross table for learning purposes

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

Not applicable

Re: Implementing Crosstable without using cross table for learning purposes

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

Not applicable

Re: Implementing Crosstable without using cross table for learning purposes

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

Community Browser