Mapping client to items

[#1]

Hi experts,

Lets see if anyone has a solution for my problem.

i'll try to explain...

I have a excel sheet like below:

Client           id

1                  socks

                    socks

                    trousers

                    shirts

                    socks

2                  ex1

                    ex2

                    ex3

                    ex4

Problem is that this doesnt get matched then...

I understand that if it my excel would have looked like below I would get a map per client and available ID's. Then I would have been able to search for an ID and get all clients that has this available. Or Clients with available ID's

client             id

1                  ex1

1                  ex2

1                  ex3

1                  ex4

2                  ex1

2                  ex2

2                  ex3

2                  ex4

Can you do this in an efficient way in Qlikview?

[trdandamudi]

You can manage this in the script. Please take a look at the attached file, Hope this helps...

Load 

If(Len(Client)>0,Client,Peek(Client)) as Client,

id,

RowNo()as RowNo;

LOAD * INLINE [

Client,           id

1,                  socks

,                   socks

,                   trousers

,                   shirts

,                   socks

2,                  ex1

,                  ex2

,                   ex3

,                   ex4

];

[#3]

Hi thanks for your reply, i tihnk youre into something here. BUT.

The excel sheet I've got is a from authorities it contains millions of 100k rows, every client has several unique sometimes IDs. So I can't manually applymap client per ID if u understand.

[trdandamudi · Accepted solution]

I am not applying anything manually. The script is taking care of the Clients automatically. Did you tried my code ? If you still think it is not correct can you please share a application and your expected output.

[#5]

This actually worked perfectly fine, thank you so much! Exactly what I needed.

Can you explain more what the script actually does? Magic man thanks!

[trdandamudi]

Happy to hear that it worked out for you...

The script that I shared with you is called Preceding Load.

Load

If(Len(Client)>0,Client,Peek(Client)) as Client,

id,

RowNo()as RowNo;

LOAD * INLINE [

Client,           id

1,                  socks

,                   socks

,                   trousers

,                   shirts

,                   socks

2,                  ex1

,                  ex2

,                   ex3

,                   ex4

];

The script will execute from bottom up. So, in this example it executes the second load first and then passes the data to the first Load statement. In the first Load statement I am checking if the length of "Client" is greater than 0. If it is greater than 0 then I am retaining the "Client" if not I am copying the "Client" from the previous record. Peek(Client) will get the "Client" from previous record. I am using RowNo() as field, so that the records are loaded in a order. Hope this helps...