Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys
In the attached file i have a source excel and output also , after applying cross table i want the output?????????
Hi,
Can you attached the sample file here? As you mention above, it hard to understand your structure.
Regards,
Sokkorn
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.....
Hi,
Can you add 1 or 2 records in the attached file? And then manipulate those data in your out put.
Regards,
Sokkorn
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