Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a Table having column like below:
ID Interested_City_X Interested_City_Y Interested_City_Z
----------------------------------------------------------------------------------------------------
1 1 0 0
2 0 1 0
3 0 0 1
Instead of above i want like this:
ID CIty
1 X
2 Y
3 Z
How it possible ,kindly suggest possible way.
b:
load * inline [
ID , Interested_City_X, Interested_City_Y, Interested_City_Z
1 , 1 , 0, 0
2 , 0 , 1, 0
3 , 0 , 0 , 1
];
t:
CrossTable(Interested_City_X, Data)
LOAD ID,
Interested_City_X,
Interested_City_Y,
Interested_City_Z
Resident b;
DROP Table b;
z:
NoConcatenate LOAD
ID,
SubField(Interested_City_X, '_', 3) as City
Resident t where Data>0;
DROP Table t;
Load * inline
[
ID ,CIty
1 , X
2 , Y
3 , Z
];
Hi Vir Vir
It sure can.
You can use the Crosstable funktion when you select your data to load in script wizard.
/Teis
You would need a CrossTable Load here:
Table:
LOAD * Inline [
ID, Interested_City_X, Interested_City_Y, Interested_City_Z
1, 1, 0, 0
2, 0, 1, 0
3, 0, 0, 1
];
CrossTable:
CrossTable(City, Data)
LOAD *
Resident Table;
NewTable:
LOAD SubField(City, '_', -1) as City,
ID
Resident CrossTable
Where Data = 1;
DROP Tables Table, CrossTable;
Hi,
CrossTable is the function to use in this case
b:
load * inline [
ID , Interested_City_X, Interested_City_Y, Interested_City_Z
1 , 1 , 0, 0
2 , 0 , 1, 0
3 , 0 , 0 , 1
];
t:
CrossTable(Interested_City_X, Data)
LOAD ID,
Interested_City_X,
Interested_City_Y,
Interested_City_Z
Resident b;
DROP Table b;
z:
NoConcatenate LOAD
ID,
SubField(Interested_City_X, '_', 3) as City
Resident t where Data>0;
DROP Table t;