Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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 😁🤗