Create multiple rows based on a column value

[Supriya4]

I have joined 2 files such that:

Table 1:

ManagerID,

ManagerName,

NoOfDirects; //Number of employees under this manager (Numeric value)

Table 2:

ManagerID,

EmployeeID,

EmployeeName;

Table1 and Table 2 are Joined on ManagerID.

Example:

ManagerID	ManagerName	NoOfDirects	EmployeeID	EmployeeName
1234	ABC	5	2341	Zbs
1234	ABC	5	7791	Fhj
1234	ABC	5	3412	Eui

Since there are 5 directs under 'ABC', but we have the details of only 3 directs(employee). I need to create the following rows.

ManagerID	ManagerName	NoOfDirects	EmployeeID	EmployeeName
1234	ABC	5	2341	Zbs
1234	ABC	5	7791	Fhj
1234	ABC	5	3412	Eui
1234	ABC	5	-	Data Not Available
1234	ABC	5	-	Data Not available

[StarinieriG]

Hi

try with this script

TABLE:
load * inline
[ManagerID,ManagerName,NoOfDirects,EmployeeID,EmployeeName
1234,ABC,5,2341,Zbs
1234,ABC,5,7791,Fhj
1234,ABC,5,3412,Eui
];

NoConcatenate
COUNT:
LOAD
ManagerID,
NR-COUNT AS NR,
ManagerName,
NoOfDirects
;
LOAD
ManagerID,
NoOfDirects AS NR,
Count(DISTINCT EmployeeID) AS COUNT,
MaxString(ManagerName) AS ManagerName,
MaxString(NoOfDirects) AS NoOfDirects
Resident TABLE
Group By
ManagerID,
NoOfDirects
;

FOR i=0 to NoOfRows('COUNT')-1

LET ManagerID = Peek('ManagerID',$(i),'COUNT');
LET ManagerName = Peek('ManagerName',$(i),'COUNT');
LET NoOfDirects = Peek('NoOfDirects',$(i),'COUNT');

LET NR = Peek('NR',$(i),'COUNT');

Concatenate (TABLE)
LOAD
'$(ManagerID)' AS ManagerID,
'$(ManagerName)' AS ManagerName,
'$(NoOfDirects)' AS NoOfDirects,
Null() AS EmployeeID,
'Data Not Available' AS EmployeeName
AutoGenerate($(NR));

NEXT

DROP Table COUNT;

[marcus_sommer · Accepted solution]

A quite similar approach in regard to the main-logic but implemented with an inner (while) loop. IMO it's a bit easier to create and in the sense of readability and if it comes to larger datasets it's much faster as an outer loop:

t1:
load * from table1;
left join(t1) load from table2;
left join(t1) load ManagerID, count(ManagerID) as EmployeeCount
       from table 2 group by ManagerID;
concatenate(t1) load distinct ManagerID, ManagerName, NoOfDirects, null() as EmployeeID,
                                                            'Data Not Available ' & iterno() as EmployeeName
       resident t1 while EmployeeCount + iterno() <= NoOfDirects;

- Marcus

[Supriya4]

Thank you soo much! It worked like a charm 😁🤗