Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I have below table
I want to convert this table into below format,
Can somebody help?
Thanks
Hi
Try like below
CrossTable(Designation, Value)
Temp:
Load * Inline
[
City, L1, L2, L3, L4, L5
Bangalore, 1, 0, 0, 1, 1
Delhi, 1, 1, 1, 0, 0
Chennai, 0, 0, 0, 1, 1
];
GenericTemp:
Generic Load Designation, City, Value Resident Temp;
LevelJoin:
Load Distinct Designation Resident Temp;
FOR i = NoOfTables()-1 to 0 STEP -1;
LET vTable=TableName($(i));
IF WildMatch('$(vTable)','GenericTemp.*') THEN
LEFT JOIN ([LevelJoin]) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
Its similar to @neerajthakur , with merge all the generic table to single table.
Hi , as below.
Data:
LOAD City as Designation,
Mumbia,
Banglore,
Delhi,
Pune
FROM SourceTable,
filters(
Transpose()
));
You can use Crosstable function.
Refer this example:
Hi
thanks for input
Cross table wont work , please see my requirement clearly ,
with crosstable I won't get desire results which i m expecting
Thanks
tmpData:
Crosstable (Designation, Values)
Load * inline [
City, L1, L2, L3, L4, L5, L6
Mumbai, 1, 0, 0, 1, 1, 0
Bangalore, 0, 0, 1, 1, 1, 0
Delhi, 1, 1, 0, 0, 0, 0
Pune, 1, 0, 0, 1, 1, 1
];
Final:
Load Designation,
City as City2,
Values
Resident tmpData;
Drop Table tmpData;
Exit Script;
After crosstable you can use Pivot Table to show data like this.
T:
CrossTable(city1,sales)
Load * Inline
[
city,L1,L2,L3
Mumbai,1,2,3
Delhi,2,3,4
Bgl,4,5,6
];
EXIT SCRIPT;
in front end use the pivot table to get desired results.
Hi everyone ,
Thanks for your all inputs
I Don't want my expected output on frontend in pivot table,
Cant that be possible on backend only
I want my expected table in backend only
Thanks
tmpData:
Crosstable (Designation, Values)
Load * inline [
City, L1, L2, L3, L4, L5, L6
Mumbai, 1, 0, 0, 1, 1, 0
Bangalore, 0, 0, 1, 1, 1, 0
Delhi, 1, 1, 0, 0, 0, 0
Pune, 1, 0, 0, 1, 1, 1
];
Generic
Final:
Load
Designation,
City as City2,
Values
Resident tmpData;
Drop Table tmpData;
Exit Script;
Hi
Try like below
CrossTable(Designation, Value)
Temp:
Load * Inline
[
City, L1, L2, L3, L4, L5
Bangalore, 1, 0, 0, 1, 1
Delhi, 1, 1, 1, 0, 0
Chennai, 0, 0, 0, 1, 1
];
GenericTemp:
Generic Load Designation, City, Value Resident Temp;
LevelJoin:
Load Distinct Designation Resident Temp;
FOR i = NoOfTables()-1 to 0 STEP -1;
LET vTable=TableName($(i));
IF WildMatch('$(vTable)','GenericTemp.*') THEN
LEFT JOIN ([LevelJoin]) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
Its similar to @neerajthakur , with merge all the generic table to single table.
Thanks @MayilVahanan !!