topic Re: Transpose fields from one column of data in QlikView

Transpose fields from one column of data

giuval45 — Sat, 16 Nov 2024 20:39:37 GMT

Hi,

I have a data set extracted from Active Directory with Logoff information of the users:

The format of this file is:

"This event is generated when a logon session is destroyed. It may be positively correlated with a logon event using the Logon ID value. Logon IDs are only unique between reboots on the same computer."""
"Information,13/06/2019 00.00.20,Microsoft-Windows-Security-Auditing,4634,Logoff,""An account was logged off."

Subject:
" Security ID: SYSTEM"
" Account Name: SYSTEM"
" Account Domain: NT AUTHORITY"
" Logon ID: 0x2BBE225A6"

"Logon Type: 9"

Subject:
" Security ID: SYSTEM"
" Account Name: SYSTEM"
" Account Domain: NT AUTHORITY"
" Logon ID: 0x2BBE229D2"

"Logon Type: 9

repeated for each gorup of records (more than 500.000 rows, for only one day...), as per the attached sample (the original is .csv, but is not possible to upload it...):

I need to obtain these fields in a flat file :

TimeStamp, SecurityID, AccountName, AccountDomain, LogonID, LogonType

13/06/2019 00.00.20,SYSTEM,SYSTEM,NT AUTHORITY,0x2BBE225A6,9

13/06/2019 00.00.20,SYSTEM,SYSTEM,NT AUTHORITY,0x2BBE229D2,9

in order to monitor all the accesses by the users.

I tried with Transpose but this move ALL the rows to one line. What is needed is a Transpose limited to 8 rows each time a sort of a "reversed Crosstab", in order to create the a/m columns of fields, with the data, for each group of Records.

Many thanks for your help.

Best regards,

Giulio

Re: Transpose fields from one column of data

Frank_Hartmann — Mon, 17 Jun 2019 11:03:52 GMT

There might be better solutions for your requirement like mine, but give it a try:

Load: LOAD RowNo()-3 as RowNum, textbetween(A,'Logon ID:','') as [Logon ID] FROM [C:\Users\admin\Downloads\Export-Test.xlsx] (ooxml, no labels, table is Foglio2) Where A<>''; left join LOAD RowNo() as RowNum, textbetween(A,'Security ID:','') as [Security ID] FROM [C:\Users\admin\Downloads\Export-Test.xlsx] (ooxml, no labels, table is Foglio2) Where A<>''; left join LOAD RowNo()+2 as RowNum, textbetween(A,'Information,',',Microsoft') as [Timestamp] FROM [C:\Users\admin\Downloads\Export-Test.xlsx] (ooxml, no labels, table is Foglio2) Where A<>''; left join LOAD RowNo()-2 as RowNum, textbetween(A,'Account Domain:','') as [Account Domain] FROM [C:\Users\admin\Downloads\Export-Test.xlsx] (ooxml, no labels, table is Foglio2) Where A<>''; left join LOAD RowNo()-1 as RowNum, textbetween(A,'Account Name:','') as [Account Name] FROM [C:\Users\admin\Downloads\Export-Test.xlsx] (ooxml, no labels, table is Foglio2) Where A<>''; left join LOAD RowNo()-4 as RowNum, textbetween(A,'Logon Type:','') as [Logon Type] FROM [C:\Users\admin\Downloads\Export-Test.xlsx] (ooxml, no labels, table is Foglio2) Where A<>''; NoConcatenate Final: Load * Resident Load Where Timestamp<>Null(); DROP Table Load; DROP Field RowNum;

Re: Transpose fields from one column of data

giuval45 — Mon, 17 Jun 2019 12:41:46 GMT

Hi Frank,

really many thanks...

Your solution is very simple, fast and "efficient"... 🙂

Waiting for a reply from the Community, I worked on it and I obtained the same result with 12 (TWELVE!) ETL passages, some quite complex and using, at the end, also a Generic Load... 😞

Just to confirm that to connect two points, a part a straight line, there are several way, some very cumbersone... like mine...

So I really appreciated the simplicity and effectiveness of your script.

Best regards,

Giulio