Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.....
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
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
Well you don't have to write them, you can just do a LOAD *
-Vlad
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....
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
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
You forgot to attach.
Vlad....i attached it in the ask box....tks!
I can't decipher your data model, sorry...