topic Re: Data iteration problem in QlikView

Data iteration problem

danieloberbilli — Thu, 06 Feb 2014 12:26:16 GMT

Dear all,

Please find attached my .qvw file with the following problem:

I need to transform my source data fields in a certain way and I am pretty sure that I need an iteration function for that.

Please be aware, that this is only a very simple and abstract way of displaying my data - the real case includes several thousand of categories and Value-Fields.

Here is the problem:

A,B,C are 3 different categories

The Value-fields contain null,0 or 1

Problem: the Value fields are not directly linked to the right category

They have a placeholders X and Y that could be A,B or C

The two fields X and Y provide the information if the value of Value_X and Value_Y belong either to A,B or C

As a result I need a Value-Field for each category:

Thanks a lot for your time and help.

Daniel

Re: Data iteration problem

marcus_sommer — Thu, 06 Feb 2014 13:53:00 GMT

Hi Daniel,

generally your example worked with quite simply if-loops. If there are thousands (or too many for typing manually) of categories I suggest to load distinct all categories and create with variables a load-statement within in a loop in a stream-structure like:

table:

ID Categorie Value

instead to create a table with thousands of columns.

for i = 0 to noofrows('DistinctCategories') - 1

vCategorie = peek('Categorie', $(i), 'DistinctCategories')

Load-Statement ...

- Marcus

Re: Data iteration problem

danieloberbilli — Sun, 09 Feb 2014 19:55:48 GMT

Thank you so much, Marcus!

In this loop structure where you mention: 'Load-Statement' ...how would this load-statement look like? Does this mean I have to generate a single temporary table for each category and concatenate all of them in the end?

It would be great if you could give me a short example of this load statement.

Thanks again!

Daniel

Re: Data iteration problem

rwunderlich — Sun, 09 Feb 2014 22:38:58 GMT

It looks basically like a GENERIC LOAD problem. See attached. It won't matter how many categories you have. However, if you have many more value columns, there may be a trick of folding the table down using CROSSTABLE followed by GENERIC. I'd have to think about it. But see if the attached gives you an idea.

More on GENERIC LOAD in the Ref Guide and here.

http://qlikviewnotes.blogspot.com/2010/05/use-cases-for-generic-load.html

-Rob

http://masterssummit.com

Re: Data iteration problem

danieloberbilli — Mon, 10 Feb 2014 19:04:53 GMT

Thank you, Rob. I think using generic loads is a smart way but as I am not familiar in creating loops/iterations in QlikView, I don't figure out how to transform your example to my case where I have several fields providing the category and several fields with the value-information.

Daniel

Re: Re: Data iteration problem

Tue, 11 Feb 2014 02:03:45 GMT

Hi Daniel, Please find the attached qvw and let me know in case of any concerns.

Re: Re: Data iteration problem

rwunderlich — Tue, 11 Feb 2014 03:59:59 GMT

Sorry Daniel, I forgot to attach the example! Here it is and it appears that Dathu has attached a similar example.

-Rob

http://masterssummit.com

Re: Data iteration problem

danieloberbilli — Tue, 11 Feb 2014 13:44:20 GMT

Thank you, Rob. Unfortunately your qvw attachment didnt include any script but as you said before...Dathu gave a similar example which works fine for me.

All the best

Daniel

Re: Data iteration problem

danieloberbilli — Tue, 11 Feb 2014 13:46:21 GMT

Excellent, Dathu! With this example I learned a lot of using iterations...thank you so much!

All the best

Daniel

Re: Data iteration problem

Tue, 11 Feb 2014 13:48:38 GMT

Your welcome.

Re: Re: Data iteration problem

rwunderlich — Tue, 11 Feb 2014 17:58:38 GMT

I'm batting zero on this one. I'm glad Dathu's example solved your issue. for the record, I've attached the correct example to my post.

-Rob