Cross Table

Wed, 04 Apr 2012 03:02:47 GMT

Hi Guys

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

Cross Table

Sokkorn — Wed, 04 Apr 2012 03:17:32 GMT

Hi,

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

Regards,

Sokkorn

Cross Table

Wed, 04 Apr 2012 03:20:12 GMT

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.....

Cross Table

Sokkorn — Wed, 04 Apr 2012 04:08:16 GMT

Hi,

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

Regards,

Sokkorn

Re: Cross Table

Sokkorn — Sat, 07 Apr 2012 03:56:48 GMT

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

topic Cross Table in QlikView

Cross Table

Cross Table

Cross Table

Cross Table

Re: Cross Table