topic Re: Interesting Challenge: How to store a table based on a specific number of rows? in QlikView

Interesting Challenge: How to store a table based on a specific number of rows?

jblomqvist — Mon, 18 Apr 2016 16:53:01 GMT

Hola amigos,

I have a interesting challenge below and the question at the end.

I have a script that looks like the following:

TableA:

LOAD

'TableA' as Source,

From source;

Concatenate(TableA)

TableB:

LOAD

'TableB' as Source,

From source;

...etc

Then I have:

NewTableA:

LOAD

Where Source = 'TableA'

Resident TableA;

STORE NewTableA into folder\NewTableA.txt(txt);

NewTableB

LOAD

Where Source = 'TableB'

Resident TableA;

STORE NewTableB into folder\NewTableB.txt(txt);

-------------------------------------

What I would like to do is to specify for each store statement how many rows to store from each table. The number of rows to store would be specified by a user.

I imagine I will need to use a variable (e.g. vTableARows, vTableBRows etc) to store the number of rows.

But how can I integrate that into my script so that the eventual text file only stores the number of rows of data for each table?

Re: Interesting Challenge: How to store a table based on a specific number of rows?

sunny_talwar — Mon, 18 Apr 2016 16:56:48 GMT

May be something like this:

TableA:

LOAD

'TableA' as Source,

RowNo() as Count,

<fields>

From source;

Concatenate(TableA)

LOAD

'TableB' as Source,

RowNo() as Count,

<fields>

From source;

...etc

Then I have:

NewTableA:

LOAD

<more fields>

Resident TableA

Where Source = 'TableA' and Count <= $(vTableARows);

STORE NewTableA into folder\NewTableA.txt(txt);

NewTableB

LOAD

<more fields>

Resident TableA

Where Source = 'TableB' and Count <= $(vTableBRows);

STORE NewTableB into folder\NewTableB.txt(txt);

Re: Interesting Challenge: How to store a table based on a specific number of rows?

Gysbert_Wassenaar — Mon, 18 Apr 2016 16:56:54 GMT

You can try using the FIRST keyword followed by a number, or in your case the dollar expanded variable:

NewTableA:

FIRST $(vTableARows) LOAD

Where Source = 'TableA'

Resident TableA;

STORE NewTableA into folder\NewTableA.txt(txt);

NewTableB

FIRST $(vTableBRows) LOAD

Where Source = 'TableB'

Resident TableA;

STORE NewTableB into folder\NewTableB.txt(txt);

Re: Interesting Challenge: How to store a table based on a specific number of rows?

jblomqvist — Mon, 18 Apr 2016 18:36:45 GMT

Hi guys

Thanks for your suggestions.

How can I set the variables to be dynamic?

E.g. If I use vTableARows variable in the script, can I create the variable in the front end and then use an inputbox to change it's values?

Or do I have to do Set vTableARows = 10 and then use the inputbox in the front end?

Re: Interesting Challenge: How to store a table based on a specific number of rows?

sunny_talwar — Mon, 18 Apr 2016 18:40:00 GMT

You can use an input box on the front end and once a value is entered the back-end should pull only the number of rows that you have entered in your variable through the inputbox