Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Cross Table

Hi Guys

In the attached file i have a source excel and output also  , after applying cross table i want  the output?????????

4 Replies
Sokkorn
Honored Contributor

Cross Table

Hi,

Can you attached the sample file here? As you mention above, it hard to understand your structure.

Regards,

Sokkorn

Not applicable

Cross Table

apply functions what bu want and then load it without using load * then u vl get all the records and u cn rearrange it as u want.....

Sokkorn
Honored Contributor

Cross Table

Hi,

Can you add 1 or 2 records in the attached file? And then manipulate those data in your out put.

Regards,

Sokkorn

Sokkorn
Honored Contributor

Re: Cross Table

Hi,

Your data source very complicate. Let try my script:

[D1]:

CrossTable(StudentName,Data)

LOAD

    ID,

    StudentName,

    IF(ISNULL(Comments),'-',Comments) AS Comments,

    School,

    Level,

    School1,

    Level1,

    School2,

    Level2

FROM

[Book5.xls]

(biff, embedded labels, header is 2 lines, table is Sheet1$);

[D2]:

LOAD

    ID    AS [StuID],

    PurgeChar(StudentName,'123456789')    as [Field],

    Data    AS [Value]

RESIDENT [D1];

//*******************************************************************//

[TMP1]:

GENERIC LOAD * RESIDENT [D2];

[RESULT]:

LOAD DISTINCT [StuID] RESIDENT [D2];

DROP TABLE [D2];

FOR i = 0 to NoOfTables()

TableList:

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

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

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

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

LEFT JOIN ([RESULT]) LOAD * RESIDENT $(vTable);

DROP TABLE $(vTable);

NEXT i

DROP TABLES TableList,D1;

[Data]:

LOAD RecNo() &'-'& StuID &'-'& School AS [Key],* RESIDENT [RESULT];

DROP TABLE [RESULT];

//**********************************************************************************//

[D1]:

CrossTable([2003],Data)

LOAD

    Column1    AS [StuID],

    [2003], 

    [2004],

    [2005]

FROM

[Book5.xls]

(biff, embedded labels, header is 1 lines, table is Sheet1$) WHERE Column1 <> 'ID';

RENAME FIELD Data TO [School_Year], [2003] TO [Years];

[Data2]:

LOAD RecNo()&'-'& StuID &'-'& [School_Year] AS [Key],[Years] RESIDENT     [D1];

DROP TABLE [D1];

See sample attached file.

Regards,

Sokkorn

Community Browser