Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load Script Question - Populate number groups

Hi,

I have the below requirement, any suggestions on how I could do this in the load script?

DummyData.PNG

For the same ID I need to create a new field which contains numbers starting from 1 and grouping by the GroupCount value (ex) the first GroupCount value is 3 so we populate the first 3 rows with 1, the second GroupCount value is 2 so we populate the next 2 rows with 2 etc...

Any help would be appreciated.

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

Final:   

Load *,RowNo() As RowNo Inline [ 

ID,GroupCount,Cost 

TEST1,3,4

TEST1,2,8 

TEST1,4,9

TEST1,,8

TEST1,,6

TEST1,,2

TEST1,,1

TEST1,,7

TEST1,,6 ]; 

 

  

Join(Final)

Load ID,Result,RowNo() As RowNo While IterNo() <= GroupCount; 

Load ID,AutoNumber(ID&GroupCount) As Result,GroupCount Resident Final Where IsNum(GroupCount);

View solution in original post

4 Replies
anbu1984
Master III
Master III

Temp:

Load *,AutoNumber(RowNo(),ID) As Result;

Load * Inline [

ID,GroupCount

TEST1,3

TEST1,2

TEST1,4 ];

Final:

NoConcatenate

Load *,RowNo() Resident Temp While IterNo() <= GroupCount;

Drop Table Temp;

Not applicable
Author

Hi Anbu,

Thanks for the above script! This script is very close to solving my issue but I spotted an issue with it. I've added a new field, Cost, below to highlight what happens when I run the script.

DummyData.PNG

So the script does create the field 'Desired Result' as requested but in doing so it replicates the data in the first 3 rows (ie) all of the data from row 1 appears three times in the output data, row 2 appears twice, etc...

What I'm after is the column 'Desired Result' but with no other changes to the existing data, so in this case Cost would remain unchanged (Desired Cost) rather than end up like the 'Current Cost' field.

I hope this makes sense and thanks again for picking this up!

Cheers,

John

anbu1984
Master III
Master III

Final:   

Load *,RowNo() As RowNo Inline [ 

ID,GroupCount,Cost 

TEST1,3,4

TEST1,2,8 

TEST1,4,9

TEST1,,8

TEST1,,6

TEST1,,2

TEST1,,1

TEST1,,7

TEST1,,6 ]; 

 

  

Join(Final)

Load ID,Result,RowNo() As RowNo While IterNo() <= GroupCount; 

Load ID,AutoNumber(ID&GroupCount) As Result,GroupCount Resident Final Where IsNum(GroupCount);

View solution in original post

Not applicable
Author

Perfect, thanks for the quick turnaround, the script worked a treat.

I tweaked it slightly as the above didn't work if the GroupCount value was the same for all rows:

AutoNumber(ID&GroupCount&RowNo)

Thanks again