topic Re: Creating a Unique ID from Multiple Rows of Data in QlikView

Creating a Unique ID from Multiple Rows of Data

mccook — Sat, 16 Nov 2024 18:26:28 GMT

Hi,

I have data a little like this:

Field	Value
IP	812
Site	North
Service	Acme
IP	813
Site	South
Service	Acme
IP	814
Site	North
Service	Bang

I know that the data that belongs together is on 3 rows, e.g. 1,2 and 3 is one record, 4,5,6 is another and so on.

What I need is:

IP	Site	Service	Unique ID (created)
812	North	Acme	1
813	South	Acme	2
814	North	Bang	3

Any advice appreciated, as I have no control over the data format I'm receiving.

Cheers,

Dean

Re: Creating a Unique ID from Multiple Rows of Data

Saravanan_Desingh — Thu, 25 Jun 2020 19:35:38 GMT

One solution is.

tab1: LOAD If(Field='IP',RangeSum(Peek(ID),1),Peek(ID)) As ID, * INLINE [ Field, Value IP, 812 Site, North Service, Acme IP, 813 Site, South Service, Acme IP, 814 Site, North Service, Bang ]; Gen: Generic LOAD * Resident tab1; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); IF WildMatch('$(vTable)', 'Gen.*') THEN LEFT JOIN (tab1) LOAD * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i

Re: Creating a Unique ID from Multiple Rows of Data

Saravanan_Desingh — Thu, 25 Jun 2020 19:36:50 GMT

Output.

Re: Creating a Unique ID from Multiple Rows of Data

Saravanan_Desingh — Thu, 25 Jun 2020 19:41:42 GMT

I missed the Unique ID.

tab1: LOAD If(Field='IP',RangeSum(Peek(ID),1),Peek(ID)) As ID, * INLINE [ Field, Value IP, 812 Site, North Service, Acme IP, 813 Site, South Service, Acme IP, 814 Site, North Service, Bang ]; Gen: Generic LOAD ID As [Unique ID], * Resident tab1; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); IF WildMatch('$(vTable)', 'Gen.*') THEN LEFT JOIN (tab1) LOAD * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i

Re: Creating a Unique ID from Multiple Rows of Data

Saravanan_Desingh — Thu, 25 Jun 2020 19:42:37 GMT

Output now.

Re: Creating a Unique ID from Multiple Rows of Data

vamsee — Thu, 25 Jun 2020 20:30:25 GMT

Hi @Saravanan_Desingh

I really liked your approach but noticed that it's creating duplicate records. (See no of rows in table viewer).

Anyway, sharing the solution as I already started working on it.

Would love to hear your comments.

Thanks

Re: Creating a Unique ID from Multiple Rows of Data

Saravanan_Desingh — Thu, 25 Jun 2020 20:39:22 GMT

Hi @vamsee . My bad. I have over looked my code.

tab1: LOAD If(Field='IP',RangeSum(Peek(ID),1),Peek(ID)) As ID, * INLINE [ Field, Value IP, 812 Site, North Service, Acme IP, 813 Site, South Service, Acme IP, 814 Site, North Service, Bang ]; tab2: LOAD 0 AutoGenerate 1; Gen: Generic LOAD ID As [Unique ID], * Resident tab1; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); IF WildMatch('$(vTable)', 'Gen.*') THEN LEFT JOIN (tab2) LOAD * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i Drop Table tab1; Drop Field [0];

Re: Creating a Unique ID from Multiple Rows of Data

Saravanan_Desingh — Thu, 25 Jun 2020 20:40:14 GMT

Can you please paste your code here? I have PE version and I could not open your QVW.

Re: Creating a Unique ID from Multiple Rows of Data

vamsee — Thu, 25 Jun 2020 20:52:24 GMT

Here you go @Saravanan_Desingh

Data: Load * , Autonumber(Rowno()&Field&Value) as Key_Column //Creating a Unique Identifier ; LOAD * INLINE [ Field, Value IP, 812 Site, North Service, Acme IP, 813 Site, South Service, Acme IP, 814 Site, North Service, Bang ]; Let i=1; For i=1 to (NoOfRows('Data')/3) //As you have data in sets of three records //Dividing the number of records by 3 Let LoopCount =(NoOfRows('Data')/3) ; Trace Loop Number $(LoopCount); Loop_table: First 3 Load '$(i)' as Key, Key_Column as Key_Exists, //To Check if the record is already loaded Field, Value Resident Data; /**********************/ GenericLabel: Generic Load Key, Field, Value Resident Loop_table; Set vListOfTables = ; For vTableNo = 0 to NoOfTables() Let vTableName = TableName($(vTableNo)) ; If Subfield(vTableName,'.',1)='GenericLabel' Then Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ; End If Next vTableNo CombinedGenericTable: Load distinct Key Resident Loop_table ; For each vTableName in $(vListOfTables) //Combining all generic tables into 1 Left Join (CombinedGenericTable) Load * Resident [$(vTableName)]; Drop Table [$(vTableName)]; Next vTableName /*******************/ Let vConcatenate=If($(i)=1, Null(), 'Concatenate (Final)'); Final: $(vConcatenate) Load *, $(i) as Dummy //Adding a dummy column so that Final does not get concatenated to CombinedGenericTable Resident CombinedGenericTable ; Drop Table CombinedGenericTable; Loop_Check: //Deleting the records which are already loaded NoConcatenate Load Key_Column, Field, Value Resident Data Where Not Exists (Key_Exists,Key_Column) //Check for records ; Drop Tables Data,Loop_table; Rename Table Loop_Check to Data; Next i Drop Table Data; //Dropping the source table(data) at the end// This should not have rows left// You can use it as check Drop Field Dummy; //Dropping the dummy field Rename Field Key to UNIQUE_ID; /** Dropping the variables **/ Let i= Null(); Let LoopCount= Null(); Let vConcatenate= Null(); Let vListOfTables= Null(); Let vTableName= Null(); Let vTableNo= Null();

Re: Creating a Unique ID from Multiple Rows of Data

Saravanan_Desingh — Thu, 25 Jun 2020 22:01:41 GMT

Wow. This is really a good effort. Nice work @vamsee . I have seen your other responses too in the Community.

Re: Creating a Unique ID from Multiple Rows of Data

vamsee — Fri, 26 Jun 2020 03:43:49 GMT

Thanks Saran

Re: Creating a Unique ID from Multiple Rows of Data

mccook — Fri, 26 Jun 2020 08:27:49 GMT

Perfect, Thanks for your help