Subfield Challenge

[marcel_olmo]

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.

[marcus_sommer]

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

[Vegar · Accepted solution]

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;

[marcel_olmo]

Thanks Vegar! It works like a charm! Nice job

[marcel_olmo]

Thanks Marcus for the answer! It was very helpful.

Regards, Marcel.

[held_florian]

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;

[held_florian]

the right mapping table 🙈