Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master
Master

Hi,

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

Regards,

Sokkorn

Not applicable
Author

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

Hi,

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

Regards,

Sokkorn

Sokkorn
Master
Master

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