topic Loop as long Modulo >0 in QlikView

Loop as long Modulo >0

leimgruber — Wed, 11 Nov 2015 08:17:14 GMT

Im struggling with the following task. I tried it with for looping and modulo but it will not work as it should.

Might someone of you already solved something similar with a nice solution or can lead me to it.

I have a user list where each user haves a integer control number This control number should end in one to many descriptions for this user.

Means the control number is a sum of several description numbers where always the biggest possible description is used first and the next biggest description has to fit into the modulo.

User:
LOAD * INLINE [
Name, ControlNumber
Norbert, 512
Heidi, 4098
Dominic, 546
];

Descriptions:
LOAD * INLINE [
Description, Number
A, 1
B, 2
C, 4
D, 8
E, 16
F, 32
G, 64
H, 128
I, 256
J, 1024
K, 2048
L, 4096
M, 8192
];

Example Dominic, 546:

512 I (first match, Modulo 34)

32 E (next match, Modulo 2)

2 B (next match, Modulo 0 therefor no more descriptions for this user)

I would like to have a table at the end like this:

UserDescriptions:

Name Description

Dominic I

Dominic E

Dominic B

Any help is welcome.

Thanks

Re: Loop as long Modulo >0

jonathandienst — Wed, 11 Nov 2015 09:16:50 GMT

This does it:

Descriptions:

LOAD * INLINE [

Description, Number

A, 1

B, 2

C, 4

D, 8

E, 16

F, 32

G, 64

H, 128

I, 256

J, 1024

K, 2048

L, 4096

M, 8192

];

// Get reverse sorted list of the codes as a string and store in a variable.

List:

LOAD Concat(Description, '', -Number) As List Resident Descriptions;

Let zList = Peek('List');

Let zLen = Len(zList);

User:

// Preceding load - discard the records where the mask was zero

LOAD *

Where Len(Code) > 0

;

// Preceding load - loop over the masks to store the code when the bit is 1

LOAD Name,

ControlNumber,

Mask,

If(Mid(Mask, IterNo(), 1) = 1, Mid('$(zList)', IterNo(), 1)) As Code

While IterNo() <= $(zLen)

;

// Load the users. Get the control number as a binary value to use as a mask.

// The mask must be right justified and padded with leading zeroes

LOAD *,

Right(Repeat('0', $(zLen)) & Num(ControlNumber, '(bin)'), $(zLen)) As Mask

INLINE [

Name, ControlNumber

Norbert, 512

Heidi, 4098

Dominic, 546

];

Note your example is incorrect. The coding for Dominic is is B (2) + F (32) + J (512).

Re: Loop as long Modulo >0

petter — Wed, 11 Nov 2015 09:54:08 GMT

This is actually straightforward bit arithmetic which could be done without the help of the descriptions table as that is just a sequence of powers of 2. I assume there is an error in the list with the missing 512 which should be the J and the remaining lines in that table are pushed "down"... then the solution could be this:

Re: Loop as long Modulo >0

leimgruber — Wed, 11 Nov 2015 11:24:05 GMT

Hi Petter

This really a great straightforward approach. It works as it should an also really fast.

In my real case the descriptions are not just letter's but I solved this with simple apply map..

If(ControlNumber bitand pow(2,IterNo()),Applymap('MAP_Desc',Chr(Ord('A')+IterNo())),Null() ) as Desc,

Many thanks

Re: Loop as long Modulo >0

petter — Wed, 11 Nov 2015 12:17:13 GMT

Cool