topic Re: Field has multiple values, need to strip out and use each one in App Development

Field has multiple values, need to strip out and use each one

spikenaylor1 — Sat, 16 Nov 2024 21:48:15 GMT

I have a sheet set like this

A B

1 Type Batch

2 Minor AB1234

3 Minor AB6789

AB9876

4 Minor CC5645

CC6786

CC9865

There is the possibility of lots of batch numbers in each field

I need to load the data such that I end up with effectively

A B

1 Type Batch

2 Minor AB1234

3 Minor AB6789

4 Minor AB9876

5 Minor CC5645

6 Minor CC6786

7 Minor CC9865

any ideas on how to achieve this?

Regards

Re: Field has multiple values, need to strip out and use each one

Gysbert_Wassenaar — Fri, 30 Nov 2018 18:38:40 GMT

Try

MyTable:
LOAD
    If(Len(Trim(Type))>0, Type, Previous(Type)) as Type,
    Batch
FROM
   MyExcelFile.xlsx (ooxml, embedded labels, table is Sheet1)
    ;

Re: Field has multiple values, need to strip out and use each one

spikenaylor1 — Mon, 03 Dec 2018 09:39:44 GMT

sorry, maybe not clear enough, in the spreadsheet, B3 contains both Batch numbers with a line break between (Alt-Enter)

B4 contains 3 batch numbers with a line break between each.

It is the Batch numbers I need to strip out and use as separate rows with the accompanying Type

regards

Re: Field has multiple values, need to strip out and use each one

Gysbert_Wassenaar — Mon, 03 Dec 2018 13:02:51 GMT

Maybe this then:

MyTable:
LOAD
    Type,
    SubField(Batch, chr(10)) as Batch
FROM
   MyExcelFile.xlsx (ooxml, embedded labels, table is Sheet1)
    ;