topic Re: Subfield Challenge in App Development

Subfield Challenge

marcel_olmo — Sat, 16 Nov 2024 20:30:14 GMT

Hi guys,

I have a table to read like this :

And I'd like to have a final result table like this :

I've been trying a lot of combinations with subfield() function to get my desired result, but I can't make it right. Anybody knows how can I do that?

I've attached an excel sample of my problem if someone can give it a try 😉

Best regards, Marcel.

Re: Subfield Challenge

marcus_sommer — Mon, 08 Jul 2019 14:19:55 GMT

I think the following will be helpful for you:

load *, rowno() as RowNo;
load *, if(Counter = 1, 'Sub-ID', CategoryT) as Category, if(Counter = 1, CategoryT, ValueT) as Value;
load *, subfield(Lines, ':', 1) as CategoryT, subfield(Lines, ':', 2) as ValueT, mod(IterNo, 4) as Counter;
load *, subfield([Detail Entries], chr(10), iterno()) as Lines, iterno() as IterNo
while iterno() <= substringcount([Detail Entries], chr(10)) + 1;
load [Purchase-Line], [Detail Entries], recno() as RecNo
from [Subfield-Challenge td-p 1599919.xlsx] (ooxml, embedded labels, table is Tabelle1);

If you really need to create multiple fields from Category and Value you could use a generic load, multiple loads with appropriate where-clauses to join them together or some further extendings/adjustments in the above matching with interrecord-functions to bring the various values into one record and deleting the unapproriate ones afterwards.

- Marcus

Re: Subfield Challenge

Vegar — Mon, 08 Jul 2019 15:04:10 GMT

A challenge it was, but this will work, I'll let you do the final touchup on splitting date and hours.

DataPrep: LOAD [Purchase-Line], replace(trim(subfield([Detail Entries], '|')), chr(10), '|') as PartDetail; LOAD [Purchase-Line], replace([Detail Entries], '2:'&chr(10),'|2:'&chr(10)) as [Detail Entries] FROM [subfield challenge.xlsx] (ooxml, embedded labels, table is Document_TB08); Data: LOAD AutoNumberHash128( [Purchase-Line], [Entry no]) as Key, [Purchase-Line], [Entry no], trim(MID( PartDetailDetail, 1, index(PartDetailDetail,':')-1)) as Header, trim(MID( PartDetailDetail, index(PartDetailDetail,':')+1)) as Value ; LOAD [Purchase-Line], left (PartDetail,1) as [Entry no], SubField(PartDetail, '|') as PartDetailDetail Resident DataPrep; ; Gen: GENERIC LOAD [Purchase-Line], [Entry no], Header , Value Resident Data WHERE not isnum(Header) //Excluding the entry number ; SET vDimensions= 'Gen.Date/Hour','Gen.Terminal','Gen.User'; //Commaseparated list of generic tables. Final: Load distinct [Purchase-Line], [Entry no] RESIDENT Data; For each vTable in $(vDimensions) Left Join (Final) Load * Resident [$(vTable)]; Drop Table [$(vTable)]; Next vTable DROP TABLE Data;

Re: Subfield Challenge

marcel_olmo — Mon, 08 Jul 2019 15:44:32 GMT

Thanks Vegar! It works like a charm! Nice job

Re: Subfield Challenge

marcel_olmo — Mon, 08 Jul 2019 15:46:32 GMT

Thanks Marcus for the answer! It was very helpful.

Regards, Marcel.

Re: Subfield Challenge

held_florian — Mon, 08 Jul 2019 15:47:00 GMT

Try this

mapSubstring:
mapping LOAD * inline [
x, y
1:, |||
2:, |||
Date/Hour:, Date/Hour
Terminal:, Terminal
Terminal :, Terminal
User:, User
];

Subfield_1:
NoConcatenate
Load
"Purchase-Line",
MapSubstring ('mapSubstring', "Detail Entries") as "Detail Entries"
Resident
Document_TB08
;

Drop Table Document_TB08;

Subfield_2:
NoConcatenate
Load
"Purchase-Line",
Subfield("Detail Entries",'|||',2) as "Detail Entries 1",
Subfield("Detail Entries",'|||',3) as "Detail Entries 2"
Resident
Subfield_1
;

Drop Table Subfield_1;

Final:
// Entry 1
NoConcatenate
Load
"Purchase-Line",
1 as Entry,
Date(floor(num(Date#(Trim(Subfield(Trim(Subfield("Detail Entries 1",'</>',3)),' ',1)),'DD/MM/YYYY'))),'DD/MM/YYYY') as Date,
Hour(num(Time#(Trim(Subfield(Trim(Subfield("Detail Entries 1",'',3)),' ',2)),'HH:MM'))) as Hour,
Trim(Subfield("Detail Entries 1",'</>',5)) as Terminal,
Trim(Subfield("Detail Entries 1",'</>',7)) as User
Resident
Subfield_2
;

Concatenate(Final)
// Entry 2
Load
"Purchase-Line",
2 as Entry,
Date(floor(num(Date#(Trim(Subfield(Trim(Subfield("Detail Entries 2",'</>',3)),' ',1)),'DD/MM/YYYY'))),'DD/MM/YYYY') as Date,
Hour(num(Time#(Trim(Subfield(Trim(Subfield("Detail Entries 2",'',3)),' ',2)),'HH:MM'))) as Hour,
Trim(Subfield("Detail Entries 2",'</>',5)) as Terminal,
Trim(Subfield("Detail Entries 2",'</>',7)) as User
Resident
Subfield_2
;

Drop Table Subfield_2;

Re: Subfield Challenge

held_florian — Tue, 09 Jul 2019 05:00:31 GMT

the right mapping table 🙈