topic Creating tables within a loop in QlikView

Creating tables within a loop

Thu, 10 May 2012 11:35:16 GMT

Hi.. I am new to qlikview..

I am trying to create tables within a loop and here is the code:..

i have a table Categories(CategoryID,CategoryName) and a table Products(ProductID,ProductName,CategoryID) which explains why i am joining the two tables to retrieve each product and their category.

Then i want to create a table for each category and input the product names beloging to that category in the corresponding tables.

SET vCategory=' ' ;

catname:

mapping LOAD ProductName as PN,CategoryID

resident Products;

join(Categories)

LOAD CategoryID

RESIDENT Categories where exists(CategoryID);

For i=0 to NoOfRows('Categories')

vCategory = Peek('CategoryID',$(i),Categories);

NoConcatenate

For j=0 to NoOfRows('Products')-1

PRODUCT_$(vCategory):

LOAD

APPLYMAP('catname',CategoryID) as ProductName;

Next j;

Next i;

I just cannot understand what is wrong with that...i tried for a whole day.. please if you can help me out

Creating tables within a loop

jagannalla — Thu, 10 May 2012 11:52:07 GMT

Hello,

I don't know what you want exactly. But when i look through your script you are using wrong way of mapping concept. Through mapping concept we should match key values perfectly. For example,

map1:

mapping load * inline [

x, y

1, one

2, two

3, three ] ;

ApplyMap ('map1', 2 ) returns ' two'

ApplyMap ('map1', 4 ) returns 4

ApplyMap ('map1', 5, 'xxx') returns 'xxx'

ApplyMap ('map1', 1, 'xxx') returns 'one'

ApplyMap ('map1', 5, null( ) ) returns NULL

ApplyMap ('map1', 3, null( ) ) returns 'three'

Re: Creating tables within a loop

Thu, 10 May 2012 12:26:12 GMT

okey...but i have a table Categories(CategoryID,CategoryName) and a table Products(ProductID,ProductName,CategoryID) and i want to create multiple tables starting with a constant like PRODUCT_<CategoryName> depending on the number of categories inside my Categories table. Then I want to fill in each table with the product which is in that category..

For example:

table PRODUCT_Baby

will have values aaa, bbb , ccc as product names

How do i go about doing this?

Re: Creating tables within a loop

jagannalla — Fri, 11 May 2012 08:07:18 GMT

I didn't get what your saying, Can you elaborate little bit more. Otherwise show your two tables data with 3 or 4 rows individually and explain what you want next generating new tables i.e ( Product_1,Product_2, Product_3 ....)

Creating tables within a loop

Fri, 11 May 2012 10:19:27 GMT

Let's say i have a table Categories(CategoryID,CategoryName) and table Products(ProductID,ProductName,CategoryID)

I want to create tables like this:

I hope it is clear enough...

Re: Creating tables within a loop

jagannalla — Fri, 11 May 2012 13:04:13 GMT

Hello,

Try the attached file.

Re: Creating tables within a loop

Sat, 12 May 2012 10:53:55 GMT

Hi Jagan, I have the personal edition of Qlikview and i have exceeded the limit of 4 attempts of recoveries. So i am not able to read your qlikview file...but thank you so much for your reply.If it is not asking to much..can you post the code directly here?