Hi Mani,
Let try this
[LogFile]:
LOAD
@1:14 AS [tmpField1],
@15:n AS [tmpField2]
FROM [Log.log]
(fix, codepage is 1252);
[Table1]:
LOAD
[tmpField1] AS _Key,
[tmpField2] AS Field1
Resident [LogFile] Where WildMatch(tmpField2,'*EmpID*');
Join (Table1)
LOAD
[tmpField1] AS _Key,
[tmpField2] AS Field2
Resident [LogFile] Where WildMatch(tmpField2,'*Status_comm*');
[Table2]:
LOAD
_Key,
Trim(SubField(FieldN,':',1)) AS FieldName,
Trim(SubField(FieldN,':',2)) AS FieldVal;
LOAD
_Key,
Trim(SubField(NewField,',')) As FieldN
;
LOAD
_Key,
//SubField(_Key,' ',1) AS RecID,
//Date(Date#(SubField(_Key,' ',2),'dd.MM.yy')) AS Dates,
Field1 & ', ' & Field2 AS NewField
Resident [Table1];
DROP Table [Table1];
[TMP1]:
Generic LOAD * Resident [Table2];
[tmpResult]:
LOAD Distinct _Key Resident [Table2];
DROP Table [Table2];
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 ([tmpResult]) LOAD * Resident $(vTable);
DROP Table $(vTable);
NEXT i
DROP Table TableList;
[Result]:
LOAD
SubField(_Key,' ',1) AS RecID,
Date(Date#(SubField(_Key,' ',2),'dd.MM.yy')) AS Dates,
*
Resident [tmpResult];
DROP Table [tmpResult];
DROP Field [_Key];
See sample attached file also.
Regards,
Sokkorn
