Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Supriya4
Contributor II
Contributor II

Create multiple rows based on a column value

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:

ManagerIDManagerNameNoOfDirectsEmployeeIDEmployeeName
1234ABC52341Zbs
1234ABC57791Fhj
1234ABC53412Eui

 

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

ManagerIDManagerNameNoOfDirectsEmployeeIDEmployeeName
1234ABC52341Zbs
1234ABC57791Fhj
1234ABC53412Eui
1234ABC5-Data Not Available
1234ABC5-Data Not available
Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

3 Replies
StarinieriG
Partner - Specialist
Partner - Specialist

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

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
Contributor II
Contributor II
Author

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