Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

how to create a resident table

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

9 Replies
vgutkovsky
Honored Contributor II

how to create a resident table

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

Not applicable

how to create a resident table

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
Honored Contributor II

how to create a resident table

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

-Vlad

Not applicable

how to create a resident table

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
Honored Contributor II

Re: how to create a resident table

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

CustomerProduct1Product2Product3
adam100

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

CustomerProductQty
adamProduct11
adamProduct20
adamProduct30

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

Not applicable

Re: how to create a resident table

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
Honored Contributor II

Re: how to create a resident table

You forgot to attach.

Not applicable

Re: how to create a resident table

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

vgutkovsky
Honored Contributor II

Re: how to create a resident table

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