topic Re: Subfield Challenge in QlikView

Subfield Challenge

marcel_olmo — Mon, 08 Jul 2019 13:16:48 GMT

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.

Re: Subfield Challenge

lockematthewp — Mon, 08 Jul 2019 14:01:40 GMT

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)

Re: Subfield Challenge

lockematthewp — Mon, 08 Jul 2019 14:03:38 GMT

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

Re: Subfield Challenge

lockematthewp — Mon, 08 Jul 2019 14:05:26 GMT

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

Re: Subfield Challenge

neelamsaroha157 — Mon, 08 Jul 2019 14:25:48 GMT

This might work. Check the attached.

Re: Subfield Challenge

ehilsinger — Tue, 09 Jul 2019 01:47:26 GMT

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];

Re: Subfield Challenge

Brett_Bleess — Wed, 31 Jul 2019 15:58:07 GMT

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