Creating a Unique ID from Multiple Rows of Data

[mccook]

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

[Saravanan_Desingh]

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

[Saravanan_Desingh]

Output.

[Saravanan_Desingh]

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

[Saravanan_Desingh]

Output now.

[vamsee]

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

[Saravanan_Desingh · Accepted solution]

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

[Saravanan_Desingh]

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

[vamsee]

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();

[Saravanan_Desingh]

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