Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marcel_olmo
Partner Ambassador
Partner Ambassador

Subfield Challenge

Hi guys,

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

Purchase-LineDetail Entries
100-11:
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-LineEntryDateHourTerminalUser
100-1114/06/201915T1User: user1@user1.com
100-1214/06/201915T100User: 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.

 

6 Replies
lockematthewp
Creator II
Creator II

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
Creator II
Creator II

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

lockematthewp
Creator II
Creator II

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

neelamsaroha157
Specialist II
Specialist II

This might work. Check the attached.

ehilsinger
Contributor III
Contributor III

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
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.