Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Dynamically load numerous tables by a dimension

Hi there,

is it possible to load Data from a datasource dynamicaly into own QV-tables separated by a dimension?

An example, the data is provided like this:
Customer, Productgroup, Status

0001, Screws, Yes

0001, Nails, No

0001, Glue, Yes

0002, Screws, Yes

0002, Nails, No

0002, Glue, Yes

0003, Screws, No

0003, Nails, No

0003, Glue, No

(Status says if a customer ever bought the product)

I would like to load an own table for each group of products automatically. In this case that would be Screws, Glue and Nails. The tables shall contain the customer number and a status, that indicates if the customer uses the product or not.

We want to see: "Who buys the screws, but never bought Nails?" For that I would set the Screw-Status to yes and the Nail-Status to No. By just loading the given table, this is not possible, I think I need own tables for each value of my dimension.

Sorry for my bad english, I hope the questions comes across. Does somebody know how to do this?

Thanks in advance and best greetings

Martin

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Dynamically load numerous tables by a dimension

There's no need to split it into several tables. You seem to be describing an "inverse crosstable load". I don't believe there's any direct support for that like there is for a crosstable load, but you can do it like this:

RawData:
LOAD * INLINE [
Customer, ProductGroup, Status
0001, Screws, Yes
0001, Nails, No
0001, Glue, Yes
0002, Screws, Yes
0002, Nails, No
0002, Glue, Yes
0003, Screws, No
0003, Nails, No
0003, Glue, No
];
ProductGroups:
LOAD fieldvalue('ProductGroup',iterno()) as ProductGroup
AUTOGENERATE 1
WHILE len(fieldvalue('ProductGroup',iterno()))
;
LET PG = peek('ProductGroup',0,'ProductGroups')
;
CustomerPurchases:
LOAD Customer, Status as $(PG)Status
RESIDENT RawData
WHERE ProductGroup = '$(PG)'
;
FOR row = 1 TO noofrows('ProductGroups') - 1
LET PG = peek('ProductGroup',row,'ProductGroups')
;
LEFT JOIN (CustomerPurchases)
LOAD Customer, Status as $(PG)Status
RESIDENT RawData
WHERE ProductGroup = '$(PG)'
;
NEXT

DROP TABLES
RawData
,ProductGroups
;

See attached for a working example using this script. Note that while you could generate the ProductGroups more simply like this...

ProductGroups:
LOAD DISTINCT ProductGroup
RESIDENT RawData
;

...the original script is much more efficient when dealing with large volumes of data.

Please let me know if I've misunderstood your requirement.

4 Replies
Not applicable

Dynamically load numerous tables by a dimension

Hi,

With the kind of data you have in source, there is no need to create another table to display it the way you have mentioned. Just create a pivot table as done in attached example.

Hope this helps.

Thanks

Amit

Not applicable

Dynamically load numerous tables by a dimension

Hi Amit,

thanks for your hint.

Apparently a pivot table does not provide the needed functionality. It displays the allocation, but does not provice the mentioned possibility to filter by each dimension AND its status.
Once selecting "Yes" for the status in the pivot, all entries (no matter the product group) are reduced to status "Yes".
After this it is not possibile to look at the entries with Status "No" of another (specific) product group.

Therefore I thought I might be clever to split this up into several tables.

Martin

MVP
MVP

Dynamically load numerous tables by a dimension

There's no need to split it into several tables. You seem to be describing an "inverse crosstable load". I don't believe there's any direct support for that like there is for a crosstable load, but you can do it like this:

RawData:
LOAD * INLINE [
Customer, ProductGroup, Status
0001, Screws, Yes
0001, Nails, No
0001, Glue, Yes
0002, Screws, Yes
0002, Nails, No
0002, Glue, Yes
0003, Screws, No
0003, Nails, No
0003, Glue, No
];
ProductGroups:
LOAD fieldvalue('ProductGroup',iterno()) as ProductGroup
AUTOGENERATE 1
WHILE len(fieldvalue('ProductGroup',iterno()))
;
LET PG = peek('ProductGroup',0,'ProductGroups')
;
CustomerPurchases:
LOAD Customer, Status as $(PG)Status
RESIDENT RawData
WHERE ProductGroup = '$(PG)'
;
FOR row = 1 TO noofrows('ProductGroups') - 1
LET PG = peek('ProductGroup',row,'ProductGroups')
;
LEFT JOIN (CustomerPurchases)
LOAD Customer, Status as $(PG)Status
RESIDENT RawData
WHERE ProductGroup = '$(PG)'
;
NEXT

DROP TABLES
RawData
,ProductGroups
;

See attached for a working example using this script. Note that while you could generate the ProductGroups more simply like this...

ProductGroups:
LOAD DISTINCT ProductGroup
RESIDENT RawData
;

...the original script is much more efficient when dealing with large volumes of data.

Please let me know if I've misunderstood your requirement.

Not applicable

Dynamically load numerous tables by a dimension

Hi John,

great, this works fine for me!
You totally got the point, thank you very much!!!

Greets,

Martin

Community Browser