Subfield Challenge

[marcel_olmo]

Hi guys,

I have a table with one field with the following structure :

Purchase-Line	Detail Entries
100-1	1: Date/Hour: 14/06/2019 15:05 Terminal:T1 User: user1@user1.com 2: Date/Hour: 14/06/2019 15:10 Terminal : T100 User: user1@user1.com

Which means that the person with the ticket 100-1 has two entries, the first one at 15:10 and the second one at 15:15 the same day.

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

Purchase-Line	Entry	Date	Hour	Terminal	User
100-1	1	14/06/2019	15	T1	User: user1@user1.com
100-1	2	14/06/2019	15	T100	User: user1@user1.com

I've tried with subfield() function with the field "Detail entries" but I can't get make it work as I want. Any ideas of how can I do that?

I've attached a sample of my problem in excel.

Regards, Marcel.

[lockematthewp]

Entry would be : subfield([Detail Entries], ':', 1)

Date

Hour  would be : subfield(subfield([Detail Entries], ':', 3), ' ', 3)

Terminal would be : subfield(subfield([Detail Entries], ':', 5), chr(10), 1)

User would be : subfield([Detail Entries], ':', 6)

[lockematthewp]

Sorry, Date is subfield(subfield([Detail Entries], ':', 3), ' ', 2)

[lockematthewp]

And be sure to use Purchase Line and Entry as dimensions and the others as expressions in your table.

[neelamsaroha157]

This might work. Check the attached.

[ehilsinger]

Pleas try the below load script.  I am unable to download your Excel file. I tried to mimic mine after your example data set.

// Split each row by the line feed.

[Temp 1]:
NoConcatenate
LOAD
[Key] as [Purchase-Line],
SubField ([Value], Chr (10)) as [Orig Value Temp]
FROM
[YOUR EXCEL FILE]
(ooxml, embedded labels, table is Sheet1);

// Change the ':' in the hour so it is not treated as a delimiter.

[Temp 2]:
NoConcatenate
LOAD
*,

If (Left ([Orig Value Temp], 9) = 'Date/Hour',
Left ([Orig Value Temp], 24) & '~' & Right ([Orig Value Temp], 2),
[Orig Value Temp]) as [Orig Value],

RowNo () as [Orig RowNo]
Resident
[Temp 1]
Where
(Len (Trim ([Orig Value Temp])) > 0);

DROP Table [Temp 1];

// Break out each entry into a attribute and value.
// Calculate a unique group for each set of data.

[Temp 3]:
NoConcatenate
LOAD
*,

Ceil ([Orig RowNo], 4) as [Group],

If (IsNum (SubField ([Orig Value], ':', 1)),
'Entry',
Trim (SubField ([Orig Value], ':', 1))) as [Attribute],

If (IsNum (SubField ([Orig Value], ':', 1)),
Trim (SubField ([Orig Value], ':', 1)),
Trim (SubField ([Orig Value], ':', 2))) as [Value]
Resident
[Temp 2]
Where
(Len (Trim ([Orig Value])) > 0);

DROP Table [Temp 2];

// Create unique fields for each attribute.

[Temp 4]:
NoConcatenate
LOAD
[Purchase-Line] as [Purchase-Line],
[Orig Value] as [Orig Value],
[Attribute] as [Attribute],
[Value] as [Value],
[Orig RowNo] as [Orig RowNo],
[Group] as [Group],

If ([Attribute] = 'Entry',
[Value],
Null ()) as [Entry],

If ([Attribute] = 'Date/Hour',
Date (Date# (Replace ([Value], '~', ':'), 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY'),
Null ()) as [Date],

If ([Attribute] = 'Date/Hour',
Time (Date# (Replace ([Value], '~', ':'), 'DD/MM/YYYY hh:mm'), 'hh'),
Null ()) as [Hour],

If ([Attribute] = 'Terminal',
[Value],
Null ()) as [Terminal],

If ([Attribute] = 'User',
[Value],
Null ()) as [User]
Resident
[Temp 3];

DROP Table [Temp 3];

// Keep the max value for each grouping.

[Temp 5]:
NoConcatenate
LOAD
MaxString ([Purchase-Line]) as [Purchase-Line],
MaxString ([Entry]) as [Entry],
Max ([Date]) as [Date],
Max ([Hour]) as [Hour],
MaxString ([Terminal]) as [Terminal],
MaxString ([User]) as [User]
Resident
[Temp 4]
Group By
[Group];

DROP Table [Temp 4];

[Brett_Bleess]

Marcel, did any of the posts help you?  If so, please click the Accept as solution button on those that did, or provide some further information.

Regards,
Brett