Sequence count of each block

[Ramu]

Hi All,

Please help me, 

I have data in this format,

In put:

LOAD * INLINE [
ID, Project
A, Proj1
A, Proj2
A, Proj3
B, Proj1
B, Proj2
B, Proj3
B, Proj4
BC, Proj1
C, Proj1
D, Proj4
E, Proj5
E, Proj6
E, Proj7
];

My Requirement is :

Out Put:

ID      SequenceNo      Count         Project

A             1                              3               Proj1
A             2                              3               Proj1, Proj2
A             3                              3               Proj1, Proj2 , Proj3
B            1                              4                Proj1
B            2                              4                Proj1,Proj2
B            3                              4                Proj1,Proj2, Proj3
B            4                              4                Proj1,Proj2, Proj3,Proj4
BC         1                              1                Proj1
C            1                              1                Proj1
D            1                              1                Proj4
E            1                              3                Proj5
E            2                              3                Proj5, Proj6
E            3                              3                Proj5, Proj6, Proj7

I achieved SequenceNo by using this formula,

AutoNumber(RowNo(),AutoNumberHash128(ID,ID)) as sequenceNo.

Please help me for last two requirements, Like Count and Project Columns.

Thanks.

[chrismarlow · Accepted solution]

Hi,

For Project try adding following to your preceding load (& then do a bit of field renaming);

	If(RowNo()=1, Project,
		If(Previous(ID)<>ID,
			Project,
			peek('Project_TEMP')&','&Project
			)
		) AS Project_TEMP

For the other one you should be able to group up & sum like you might in SQL & then join back (just loading into a temporary table that you then drop).

Cheers,

Chris.

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD Seq#,ID,If(ID<>Previous(ID),Project,Peek(Project)&','&Project) As Project
;
LOAD *, AutoNumber(RecNo(),ID) As Seq# INLINE [
ID, Project
A, Proj1
A, Proj2
A, Proj3
B, Proj1
B, Proj2
B, Proj3
B, Proj4
BC, Proj1
C, Proj1
D, Proj4
E, Proj5
E, Proj6
E, Proj7
];
Left Join(tab1)
LOAD ID, Count(Project) As Count
Resident tab1
Group By ID
;

[Saravanan_Desingh · Accepted solution]

[Saravanan_Desingh · Accepted solution]

Using Hierarchy.

tab1:
Hierarchy(Project, ParentProj,Node,,Project,Path,',')
LOAD ID, Seq#, ID&'-'&Project As Project
	, If(ID=Previous(ID),Peek(Project)) As ParentProj
	, ID&'-'&Project As Node
;
LOAD *, AutoNumber(RecNo(),ID) As Seq# INLINE [
ID, Project
A, Proj1
A, Proj2
A, Proj3
B, Proj1
B, Proj2
B, Proj3
B, Proj4
BC, Proj1
C, Proj1
D, Proj4
E, Proj5
E, Proj6
E, Proj7
];

Left Join(tab1)
LOAD ID, Count(Project) As Count
Resident tab1
Group By ID
;

[Saravanan_Desingh]

[Ramu]

Hi Chrismarlow,

Thank you so much.

[Ramu]

Hi Saran,

Thank you so much.

[Saravanan_Desingh]

Better usage of Hierarchy.

tab1:
Hierarchy(ID1, ParentID1,Node,,Project,Path,',')
LOAD ID, Project, Seq#, ID1 
	, If(ID=Previous(ID),Peek(ID1)) As ParentID1
	, Project As Node
;
LOAD *, ID&'-'&Seq# As ID1;
LOAD *, AutoNumber(RecNo(),ID) As Seq# INLINE [
ID, Project
A, Proj1
A, Proj2
A, Proj3
B, Proj1
B, Proj2
B, Proj3
B, Proj4
BC, Proj1
C, Proj1
D, Proj4
E, Proj5
E, Proj6
E, Proj7
];

Left Join(tab1)
LOAD ID, Count(Project) As Count
Resident tab1
Group By ID
;

[Saravanan_Desingh]