topic how to create a resident table in QlikView

how to create a resident table

Wed, 16 Nov 2011 13:44:53 GMT

hi everyone,

i want to know if i can create a resident table in qlikview like table 4 as i show in example.xls

Thanks for any suggestions.....

how to create a resident table

vgutkovsky — Wed, 16 Nov 2011 15:46:52 GMT

Simplest way to do this is with a join and crosstable:

realtable1:

CROSSTABLE (Product,Qty)

LOAD

Customer,

Product 1,

Product 2,

Product 3

FROM ...;

realtable2:

crosstable(Category,Category_flag)

LOAD

Product,

Category 1,

Category 2,

Category 3

FROM ...;

Now the tables will be joined on Product. You can do a join from one to the other and manipulate it in the usual way.

Regards,

Vlad

how to create a resident table

Wed, 16 Nov 2011 17:08:50 GMT

hi vlad,

thanks for your response.

if i have more than 200 products i need to write all of them? i need something dynimac because all months we have new products....

regards,

gerardo

how to create a resident table

vgutkovsky — Wed, 16 Nov 2011 18:48:24 GMT

Well you don't have to write them, you can just do a LOAD *

-Vlad

how to create a resident table

Wed, 16 Nov 2011 19:33:04 GMT

i think im doing samething wrong.....because the result of first crosstable give me the name of columns and not their values....

you mean do this, dont you?

realtable1:

CROSSTABLE (Product,Qty)

LOAD *

FROM ...;

sorry but im new with qlikview....

Re: how to create a resident table

vgutkovsky — Wed, 16 Nov 2011 19:48:47 GMT

Right, that's what a crosstable does. In your case, the column names are actually Products. So for your first row:

Customer	Product1	Product2	Product3
adam	1	0	0

Customer "adam" bought 1 unit of Product 1. After the crosstable, you will see this:

Customer	Product	Qty
adam	Product1	1
adam	Product2	0
adam	Product3	0

This will allow you to create a linking relationship to the other table, which also has "Product" as a field and Product Name as a value. By the way, as you can see, you will get a lot of records after the first crosstable with 0 Qty. To make sure you don't create a false relationship, do a Resident load from realtable1 after the crosstable to keep only records WHERE qty>0;

Regards,

Vlad

Re: how to create a resident table

Mon, 21 Nov 2011 15:24:21 GMT

hi Vlad,

i was reading about crosstable feature but i can't resolve my problem.

All examples talk about excel fields and i have .qdv files with a different estructure. I mean:

1) my 4 tables from excel files are really .qdv files with more data.

2) i need to combine 3 .qdv

i attach the .qvw file im using.

thank for any advice

Re: how to create a resident table

vgutkovsky — Mon, 28 Nov 2011 22:35:17 GMT

You forgot to attach.

Re: how to create a resident table

Tue, 29 Nov 2011 13:37:54 GMT

Vlad....i attached it in the ask box....tks!

Re: how to create a resident table

vgutkovsky — Tue, 29 Nov 2011 18:46:38 GMT

I can't decipher your data model, sorry...