Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
;
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
;
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.
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
;
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
;
Hi Chrismarlow,
Thank you so much.
Hi Saran,
Thank you so much.
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
;