How to create a test data application based on Excel spreadsheet information like this?

[mwallman]

Hi all,

Attached is an Excel spreadsheet specifying requirements of a test data application to be generated.

• The Fieldscolumn specifies the fields to be generated in a table.
• The NoOfRowscolumn specifies the number of rows to be generated.
• The values to be assigned for the fields from the Fields column are specified in the appropriate sheets, the correct sheet to use is specified in the Locationcolumn above.

The idea is to allow the end user to modify the values in an excel spreadsheet like above, and next time the QlikView application reloads, it generates a table with the appropriate values.

Does anyone know how this type of app can be generated based on the data above please?

I am a bit stuck on ideas after trying a few things but cannot get it to work.

[sunny_talwar]

May be look at Stefan's response here: Re: Interesting challenge: How to create a real table from a list of field names?

[mwallman]

Hi Sunny,

Thanks I had a look at that but I couldn't quite get the solution to work in a sense that the requirement changed because the users had to make too many changes to the script too.

How could I get it to work with this current format?

[sunny_talwar]

I think it would be best if I let the Master do the honors here: swuehl‌

[mwallman]

Thanks Sunny and swuehl,

Even if someone can show me a working demo example of this it would help me steer some ideas to the users.

[sunny_talwar]

I am leaving for work, if he doesn't respond by the time I reach work, I will try to respond (using his logic)

[swuehl]

Do you expect in the future also more fields added to the fields column with new sheets added and an arbitrary logic stated in Notes column? Still no changes allowed to the script?

Or are the field names and calculation logic fixed and only the range of values change within the following sheets?

[swuehl]

Even if someone can show me a working demo example of this it would help me steer some ideas to the users.

Michael,

if you have a copy of 'Mastering QlikView' by Stephen Redmond around, there is a working code example in chapter one 'Generating test data' that comes pretty close to what you want to achieve.

[mwallman]

Hi Swuehl,

Yes I expect the users to add more columns possibly in future.

For example they might add CountryID, and CountryName fields.

But I have made them aware that the list of these countries would have to be added to another sheet in the Excel spreadsheet AND the script would have to be modified to load that new sheet.

This the users are willing to do as a per-requisite for generating test data.

[swuehl]

Michael Wallman wrote:

Hi Swuehl,

Yes I expect the users to add more columns possibly in future.

For example they might add CountryID, and CountryName fields.

But I have made them aware that the list of these countries would have to be added to another sheet in the Excel spreadsheet AND the script would have to be modified to load that new sheet.

This the users are willing to do as a per-requisite for generating test data.

It's not just about loading the new sheets.

You would either need to create a quite complex script and use a defined syntax in your Fields sheet (using more columns / keywords) instead of just a textual representation of your value creation logic or manually create the new logic for new sheets in the script (which might not be doable by your users).

Even if you tend to go for the first option, you would need to prepare a complete set of possible value creation rules now, or you need to adapt again and again the script to changing user requirements.

After all, maybe QV is just not the right tool for doing that, have you considered also using something more specialized in creating sample data?