Creating a seperate field from a column

[madhu_r]

Hi All,

I want to create a new field from the column Particulars. The new field should have the values A, B, C & D and when I click on the field A I should get the corresponding Income value for this.

Thanks & Regards,

Madhu

[brunobertels]

hi 

Try to add this  script 

we use peek function to create field "GOUPE" with ABCD as value :

[Table]:
LOAD * INLINE
[
Particulars;Apr-19
A;
Incomes ; 4,66
Invoices;22
Penetration;50,0%
Value;367
;
B;
Incomes ; 3,77
Invoices;13
Penetration;61,5%
Value;239
;
C;
Incomes ; 8,43
Invoices; 35
Penetration;54,3%
Value; 606
;
D;
Incomes ; -
Invoices;1
Penetration;0,0%
Value;0
;
](delimiter is ';');

noconcatenate
Table_temp:
load
// create Field GROUPE With Value ABCD:
If( len(trim(Particulars))>0 and WildMatch(Particulars,'A','B','C','D') , Particulars,peek([GROUPE])) as [GROUPE],
Particulars,
[Apr-19]

Resident [Table];
drop table Table;

NoConcatenate
//reduce rows with no value
Table_Final:

load
GROUPE,
Particulars,
[Apr-19]
resident Table_temp
where [Apr-19] > 0;
drop table Table_temp;

[StarinieriG]

Hi,

you could try with intervalmatch

temp:
LOAD Particulars,
RowNo() as ID
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

NoConcatenate
INTERVAL:
LOAD
Particulars,
ID AS START,
RowNo() AS ID_1
Resident temp
Where ID=1 or Len(Trim(Previous(Particulars)))=0
;
Left Join (INTERVAL)
LOAD
RowNo() AS ID_1,
ID AS END
Resident temp
Where Len(Trim(Particulars))=0;

NoConcatenate
TABLE:
LOAD
Particulars AS PAR,
ID
Resident temp
Where (ID<>1 and Len(Trim(Previous(Particulars)))>0)
and Len(Trim(Particulars))>0;

Left Join (TABLE)
IntervalMatch (ID)
LOAD
START,
END
Resident INTERVAL;

Left Join (TABLE)
LOAD
START,
END,
Particulars AS NEW_FIELD
Resident INTERVAL;

DROP Fields START, END;

DROP Table INTERVAL;

DROP Table temp;