topic Mapping client to items in QlikView

Mapping client to items

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Mapping client to items

trdandamudi — Thu, 03 Nov 2016 10:21:26 GMT

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

];

Re: Mapping client to items

Thu, 03 Nov 2016 10:29:05 GMT

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.

Re: Mapping client to items

trdandamudi — Thu, 03 Nov 2016 10:43:08 GMT

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.

Re: Mapping client to items

Thu, 03 Nov 2016 12:21:37 GMT

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!

Re: Mapping client to items

trdandamudi — Thu, 03 Nov 2016 12:40:33 GMT

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...