topic Re: Create calculated Table in Script with allocation keys in QlikView

Create calculated Table in Script with allocation keys

Mon, 18 May 2015 08:04:54 GMT

Good Morning everybody,

I have a question regarding the creation of a table in the script, from which one field is calucalted.

A join to the existing table would not be good.

We have eight different keys and for every key we Need a table or one Table with all keys.

I.e.

I have a Turnover-Table sorted by Code H (i.e. 14001,13001 and so on) --> see screen.

I have the EUR-Amount and a calculated %(T1-Key)

The %(T1-Key) ist calculated with in the Script

Set Var_T1_Key =(sum(if (Add5 = 'Gross Sales', Valuehome,0))/aggr(NODISTINCT sum(Valuehome),CODE_C_Code));

Now the Challenge is:

I need to create a new table with the Information of the calculated keys:

Field 1: CODE_H_Code

Field 2: T1-Key

Field 3, Year-Period-Field (will be included later)

Re: Create calculated Table in Script with allocation keys

datanibbler — Mon, 18 May 2015 08:14:41 GMT

Hi Christian,

you will have to build a FOR ... TO loop for such a thing:

- First you do a FIRST 1 LOAD with those three fields you want in your new table

- Then you do such a loop with the NoOfRows() function (rows in your original table) as the max (so it will read

>> FOR i = 0 TO (NoOfRows('

')-1) <<

- In every iteration, you can use the CONCATENATE option to append the current Code_H and T1_Key to this table

=> After the loop has completed, you should have the same nr. of rows as in your original table (plus 1, which is the empty row you have extracted for a dummy, you can throw this out now)

HTH

Re: Create calculated Table in Script with allocation keys

Peter_Cammaert — Mon, 18 May 2015 08:43:07 GMT

You mean, you want to write some script code that does the same thing as the P&L Pivot Table, right?

Re: Create calculated Table in Script with allocation keys

Mon, 18 May 2015 09:44:28 GMT

Hi Peter,

the T1-Key is already calculated in the Script.

[The PivotTable show the turnover in one column and the other column show the T1-Key.]

I now want to write the result of the calculation as value into a new table with the 3 columns mentioned above (CODE_H_Code, T1-Key and Year-Period).

The Advantage of that would be a table with all keys for all month that I can easily use whenever I want without duplicating that Formular in every Report (Pivot).

I will attach for you the file I use. The Sheet Umsatz PL10xxx (Filtered on DEDETDEC) shows the database.

Maybe this will help to understand.

Very much thanks in advance.

Re: Create calculated Table in Script with allocation keys

Peter_Cammaert — Mon, 18 May 2015 10:54:50 GMT

I don't think the T1_Key value is precalculated in the script. There is a SET statement in tab Keys, but that doesn't do anything except setting a variable to a string value. The actual calculation will be done in the Pivot Table object.

I think the best way to approach this chalenge is to copy fields Valuehome, E14 and Docdate of all rows from PL10110_Umsatzbuchungen into a new table in your script. Then create a mapping table that maps E14 to CODE_H_CODE in Fire.xls_Budgetgruppen. Now add CODE_H_CODE to your new internal table. You may have to drop all rows that have no mapping, but that depends on which transactions should be included in your table.

Then perform a RESIDENT LOAD of Sum(Valuehome) with a GROUP BY on CODE_H_CODE and your preferred translation of Docdate (MonthYear or something).

Next calculate a variable that sums the value of Valuehome in all rows. Call it vTotalSum.

Reload your new table and add ValueHome / vTotalSum as T1_Key.

Is this something you can work on? If not, just ask. I don't have enough information (or data, or time) to do it myself in your document, but it doesn't look that difficult.

Peter

Re: Create calculated Table in Script with allocation keys

Mon, 18 May 2015 12:03:26 GMT

Hi Peter,

I have to think about this.

At the Moment it is not clear to me, why do I have to load twice.

At the Moment there are only Sales loaded. The costs will follow later.

At exactly this costs which dont have El4 have to be splitted to CODE_H_Code.

That is the reason why I want to calculate this keys from the one database,, without loading twice.

Do you think this is possible.

Greetings