Re: Concat field - Qlik Community

rajeshqvd · ‎2018-12-04

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;

sunny_talwar · ‎2018-12-04

Did this not work for you?

Script creating new field

rajeshqvd · ‎2018-12-04

No Stalwar if id has single area it should come single name other wise it come multiple area names with slash "/".....Thanks for response

sunny_talwar · ‎2018-12-04

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;

rajeshqvd · ‎2018-12-04

Thanks for the response....try with "subid" and 555 id giving wrong data it should come AFG only not AFG/AFG/AFG.

sunny_talwar · ‎2018-12-04

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;

Concat field

Qlik Sence