how to create a resident table

[#1]

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

[vgutkovsky]

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

[#3]

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

[vgutkovsky]

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

-Vlad

[#5]

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

[vgutkovsky]

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

[#7]

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

[vgutkovsky]

You forgot to attach.

[#9]

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

[vgutkovsky]

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