How to implement loops in QS

[Shahzad_Ahsan]

Hi All

I have data like this

ID	Value
1	2
1	2
1	3
2	7
2	9
2	6
3	7
3	9
3	4

I need to add a column in data load editor like this

Id	Value	Flag
1	2	A
1	2	B
1	3	C
2	7	A
2	9	B
2	6	C
3	7	A
3	9	B
3	4	C

There will be always 3 rows of each Id.

[Quy_Nguyen]

Not sure it matches your business. Because there will be always 3 rows of each ID, this trick may work:

Load *, Pick(Mod(RecNo(),3)+1,'C','A','B') As Flag;
LOAD * INLINE [
ID Value
1 2
1 2
1 3
2 7
2 9
2 6
3 7
3 9
3 4
];

[Shahzad_Ahsan]

Hi Quy

Thank you for your prompt response

This is working fine.

I have one more question

What about 4 rows repeating ?? Like 1 Id repeating 4 times and need to add Flag "D"  

[vunguyenq89 · Accepted solution]

You can implement this as follows:

1. Sort your raw data ascending by ID, then by Value

2. For each row:

• Look at ID value of the current row.
• If ID value is the same with previous row, then peek the previous Flag and increase by 1
• Otherwise (new ID), then reset Flag to 'A'

The piece of script below also works if number of rows for each ID is not equal.

Data_Raw:
LOAD * INLINE [
    ID, Value
    1, 2
    1, 2
    1, 3
    2, 7
    2, 9
    2, 6
    3, 7
    3, 9
    3, 4
];

Data_Final:
LOAD ID,
Value,
If(ID = Previous(ID), Chr(Ord(Peek(Flag)) + 1), 'A') as Flag
Resident Data_Raw
ORDER BY ID, Value;
DROP TABLE Data_Raw;

[Quy_Nguyen]

like this:

A:

Load *, Pick(Mod(RecNo(),4)+1,'D','A','B','C') As Flag;

LOAD * INLINE [

ID,Value

1 ,2

1 ,2

1 ,3

1 ,5

2 ,7

2 ,9

2 ,6

2 ,8

3 ,7

3 ,9

3 ,4

3 ,6

];

[Quy_Nguyen]

Incase you got different repeat per ID, Vu's answer below is the best solution.

[rwunderlich]

You can also generate the value with autonumber, in this case you won't have to worry about sort order or number of values (up to 26 A-Z).

Data: 

LOAD

  *,

  chr(64 + AutoNumber(RecNo(), ID)) as Flag

INLINE [ 

    ID, Value 

    1, 2 

    1, 2 

    2, 9 

    2, 6    

    1, 3 

    1, 3 

    2, 7 

    3, 7 

    3, 9 

    3, 4 

];

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

[Shahzad_Ahsan]

Thanks Vu

This is perfectly fine

[Shahzad_Ahsan]

Hi Rob

Thanks for your reply.

This is also correct solution