Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

Creating a Unique ID from Multiple Rows of Data

Hi,

I have data a little like this:

FieldValue
IP812
SiteNorth
ServiceAcme
IP813
SiteSouth
ServiceAcme
IP814
SiteNorth
ServiceBang

 

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:

IPSiteServiceUnique ID (created)
812NorthAcme1
813SouthAcme2
814NorthBang3

 

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

Cheers,

Dean

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

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

View solution in original post

11 Replies
Highlighted
Specialist III
Specialist III

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
Highlighted
Specialist III
Specialist III

Output.

commQV79.PNG

Highlighted
Specialist III
Specialist III

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

 

Highlighted
Specialist III
Specialist III

Output now.

commQV80.PNG

Highlighted
Specialist
Specialist

Hi @saran7de  

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

Highlighted
Specialist III
Specialist III

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

View solution in original post

Highlighted
Specialist III
Specialist III

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

Highlighted
Specialist
Specialist

Here you go @saran7de 

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();
Highlighted
Specialist III
Specialist III

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