Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello every one need help to create field "Required Otput",
ID | Subid | Area | Output | Clean | Final | Required Output | |
333 | 129 | IND PAK AUS | IND PAK AUS | IND/PAK/AUS | IND/PAK/AUS | ||
333 | 130 | IND PAK AUS | IND PAK AUS | IND/PAK/AUS | IND/PAK/AUS | ||
333 | 131 | IND PAK AUS | IND PAK AUS | IND/PAK/AUS | IND/PAK/AUS | ||
333 | 132 | IND PAK AUS | IND PAK AUS | IND/PAK/AUS | IND/PAK/AUS | ||
333 | 133 | IND PAK AUS | IND PAK AUS | IND/PAK/AUS | IND/PAK/AUS | ||
333 | 134 | IND PAK AUS | IND PAK AUS | IND/PAK/AUS | IND/PAK/AUS | ||
333 | 135 | IND PAK AUS | IND PAK AUS | IND/PAK/AUS | IND/PAK/AUS | ||
444 | 2 | PAK US | PAK US | PAK/US | PAK/US | ||
444 | 3 | PAK | PAK US | PAK US | PAK/US | PAK/US | |
444 | 4 | US | PAK US | PAK US | PAK/US | PAK/US | |
555 | 1 | AFG | AFG AFG AFG | AFG AFG AFG | AFG/AFG/AFG | AFG | |
555 | 2 | AFG | AFG AFG AFG | AFG AFG AFG | AFG/AFG/AFG | AFG | |
555 | 3 | AFG | AFG AFG AFG | AFG AFG AFG | AFG/AFG/AFG | AFG |
Script Below :
SpaceMap:
Mapping LOAD repeat(' ', RecNo()), ' '
AutoGenerate 125;
Table:
LOAD * INLINE [
ID, Subid, Area
111, 1,
222, 1,
333,1,IND
333,2,PAK
333,3,
333,4,
333,5,
333,6,
333,7,
333,8,
333,9,
333,10,
333,11,
333,12,
333,13,
333,14,
333,15,
333,16,
333,17,
333,18,
333,19,
333,20,
333,21,
333,22,
333,23,
333,24,
333,25,
333,26,
333,27,
333,28,
333,29,
333,30,
333,31,
333,32,
333,33,
333,34,
333,35,
333,36,
333,37,
333,38,
333,39,
333,40,
333,41,
333,42,
333,43,
333,44,
333,45,
333,46,
333,47,
333,48,
333,49,
333,50,
333,51,
333,52,
333,53,
333,54,
333,55,
333,56,
333,57,
333,58,
333,59,
333,60,
333,61,
333,62,
333,63,
333,64,
333,65,
333,66,
333,67,
333,68,
333,69,
333,70,
333,71,
333,72,
333,73,
333,74,
333,75,
333,76,
333,77,
333,78,
333,79,
333,80,
333,81,
333,82,
333,83,
333,84,
333,85,
333,86,
333,87,
333,88,
333,89,
333,90,
333,91,
333,92,
333,93,
333,94,
333,95,
333,96,
333,97,
333,98,
333,99,
333,100,
333,101,
333,102,
333,103,
333,104,
333,105,
333,106,
333,107,
333,108,
333,109,
333,110,
333,111,
333,112,
333,113,
333,114,
333,115,
333,116,
333,117,
333,118,
333,119,
333,120,
333,121,
333,122,
333,123,
333,124,
333,125,
333,126,
333,127,
333,128,
333,129,
333,130,
333,131,
333,132,
333,133,
333,134,
333,135,
333, 18,AUS
444, 2,
444, 3,PAK
444, 4,US
555,1,AFG
555,2,AFG
555,3,AFG
];
Left Join (Table)
LOAD ID,
Concat( DISTINCT Area,' ', Subid) as Output
Resident Table
Group By ID;
Table1:
Load *,
Replace(Clean,' ','/') as Final;
Load *,
MapSubString('SpaceMap', trim(Output)) as Clean
Resident Table;
DROP Table Table;
Did this not work for you?
This was happenning because when loading from inline load.... the blanks come in as white space... for your original data... if the data is null... you should have been fine... anyways... try this if the data is white space
Table: LOAD * INLINE [ ID, Subid, Area 111, 1 222, 1 333, 1, IND 333, 2, PAK 333, 3 333, 4 333, 5 333, 6 333, 7 333, 8 333, 9 333, 10 333, 11 333, 12 333, 13 333, 14 333, 15 333, 16 333, 17 333, 18 333, 19 333, 20 333, 21 333, 22 333, 23 333, 24 333, 25 333, 26 333, 27 333, 28 333, 29 333, 30 333, 31 333, 32 333, 33 333, 34 333, 35 333, 36 333, 37 333, 38 333, 39 333, 40 333, 41 333, 42 333, 43 333, 44 333, 45 333, 46 333, 47 333, 48 333, 49 333, 50 333, 51 333, 52 333, 53 333, 54 333, 55 333, 56 333, 57 333, 58 333, 59 333, 60 333, 61 333, 62 333, 63 333, 64 333, 65 333, 66 333, 67 333, 68 333, 69 333, 70 333, 71 333, 72 333, 73 333, 74 333, 75 333, 76 333, 77 333, 78 333, 79 333, 80 333, 81 333, 82 333, 83 333, 84 333, 85 333, 86 333, 87 333, 88 333, 89 333, 90 333, 91 333, 92 333, 93 333, 94 333, 95 333, 96 333, 97 333, 98 333, 99 333, 100 333, 101 333, 102 333, 103 333, 104 333, 105 333, 106 333, 107 333, 108 333, 109 333, 110 333, 111 333, 112 333, 113 333, 114 333, 115 333, 116 333, 117 333, 118 333, 119 333, 120 333, 121 333, 122 333, 123 333, 124 333, 125 333, 126 333, 127 333, 128 333, 129 333, 130 333, 131 333, 132 333, 133 333, 134 333, 135 333, 18, AUS 444, 2 444, 3, PAK 444, 4, US 555, 1, AFG 555, 2, AFG 555, 3, AFG ]; Left Join (Table) LOAD ID, Concat(DISTINCT If(Len(Trim(Area)) > 0, Area), '/', Subid) as Output Resident Table Group By ID;
Try this
Table: LOAD ID, Subid, If(Len(Trim(Area)) > 0, Area) as Area; LOAD * INLINE [ ID, Subid, Area 111, 1 222, 1 333, 1, IND 333, 2, PAK 333, 3 333, 4 333, 5 333, 6 333, 7 333, 8 333, 9 333, 10 333, 11 333, 12 333, 13 333, 14 333, 15 333, 16 333, 17 333, 18 333, 19 333, 20 333, 21 333, 22 333, 23 333, 24 333, 25 333, 26 333, 27 333, 28 333, 29 333, 30 333, 31 333, 32 333, 33 333, 34 333, 35 333, 36 333, 37 333, 38 333, 39 333, 40 333, 41 333, 42 333, 43 333, 44 333, 45 333, 46 333, 47 333, 48 333, 49 333, 50 333, 51 333, 52 333, 53 333, 54 333, 55 333, 56 333, 57 333, 58 333, 59 333, 60 333, 61 333, 62 333, 63 333, 64 333, 65 333, 66 333, 67 333, 68 333, 69 333, 70 333, 71 333, 72 333, 73 333, 74 333, 75 333, 76 333, 77 333, 78 333, 79 333, 80 333, 81 333, 82 333, 83 333, 84 333, 85 333, 86 333, 87 333, 88 333, 89 333, 90 333, 91 333, 92 333, 93 333, 94 333, 95 333, 96 333, 97 333, 98 333, 99 333, 100 333, 101 333, 102 333, 103 333, 104 333, 105 333, 106 333, 107 333, 108 333, 109 333, 110 333, 111 333, 112 333, 113 333, 114 333, 115 333, 116 333, 117 333, 118 333, 119 333, 120 333, 121 333, 122 333, 123 333, 124 333, 125 333, 126 333, 127 333, 128 333, 129 333, 130 333, 131 333, 132 333, 133 333, 134 333, 135 333, 18, AUS 444, 2 444, 3, PAK 444, 4, US 555, 1, AFG 555, 2, AFG 555, 3, AFG ]; Left Join (Table) LOAD ID, Area, Max(Subid) as Max_Subid Resident Table Group By ID, Area; Left Join (Table) LOAD ID, Concat(DISTINCT If(Len(Trim(Area)) > 0, Area), '/', Max_Subid) as Output Resident Table Group By ID;