topic Scripting problem... in QlikView

Scripting problem...

Anonymous — Wed, 21 Sep 2011 13:50:23 GMT

Hello everyone,

I have the following piece of script (I have simplified it, the first 2 sheets work perfectly fine):

Sheet1:

Table1
%Customer_Key,

CustomerNumberKNA1 (consisting following data: 10, 20, 50, 90, 100, 150 and 200)

from ***.qvd

Sheet 2:

Table2:

%BUS_key,
[BUS - Code & Name] (consisting following data: 2A, 2B, 2C and 2D)

from ***.qvd

Next, I want to make the following calculation:

if([BUS - Code & Name]=2A,TT,if([BUS - Code & Name]=2B,if(CustomerNumberKNA1>100,TT,None),None))

Meaning that

* Every 2A = 'TT'

* If it is 2B and the CustomerNr > 100 it is 'TT' as well

* If it is not 2A or 2B or if it is 2B but the CustomerNr < 100 it is 'None'

Any idea how I can make that calculation? I can not simply concatenate the two tables, because I am using the two '%keys' later on in the script in one and the same table (so if I concatenate, it will connect to that table two times, because of the two keys).

Hopefully I have explained it so that you guys understand it, thanks in advance!

Re: Scripting problem...

rwunderlich — Wed, 21 Sep 2011 14:17:51 GMT

I don't see the link between Table1 and Table2, but I'll assume there is one. You have several alternatives.

1. Use the lookup() function.

2. Join CustomerNumberKNA1 Table2, compute the new field and then drop CustomerNumberKNA1 from Table2.

3. Use ApplyMap(). In this example I'll assume %BUS_key exists in Table1.

custmap:

MAPPING LOAD %BUS_key, CustomerNumberKNA1 RESIDENT Table1;

In the table2 load:

if([BUS - Code & Name]=2A,TT

,if([BUS - Code & Name]=2B,if(ApplyMap('custmap',%BUS_key) >100,TT,None),None))

-Rob

Scripting problem...

SunilChauhan — Wed, 21 Sep 2011 14:22:35 GMT

check for sheet1 and sheet 2 table.

if two field have same type of data then make their name same so that link will create

and then apply this

if([BUS - Code & Name]=2A,'TT',if([BUS - Code & Name]=2B,if(CustomerNumberKNA1>100,'TT',None),None))

Scripting problem...

Anonymous — Wed, 21 Sep 2011 14:38:03 GMT

Hello Rob,

there is no direct connection between table 1 and table 2.

The two tables consist data coming from SAP, concerning Customer Data (Table 1) and Product Data (Table 2).

Eventually (later on in the script) the both keys are named when loading the CO-PA table from SAP (the main table, consisting of all accounting data). At that moment, via the keys, the two tables are connected:

Table 1 or Customer Data (a.o. %Customer_Key)

Table 2 or Product Data (a.o. %BUS_key)

CO-PA (a.o. %Customer_Key and %BUS_key)

Hope the situation is more clear now, I can't get one of the 3 options named working at the moment.

And Sunil: I can't have a field named the same in the both tables. This way, when the link is created via the CO-PA table later on, I get a loop:

Table 1 or Customer Data (a.o. %Customer_Key & Field A)

Table 2 or Product Data (a.o. %BUS_key & Field A)

CO-PA (a.o. %Customer_Key and %BUS_key)

This way, the two tables connect

- indirectly via the keys in the CO-PA table

- directly via the 'Field A'

Re: Scripting problem...

rwunderlich — Wed, 21 Sep 2011 15:28:12 GMT

So can you use option 2 or 3 using field CO-PA as the link?

-Rob

Scripting problem...

Anonymous — Thu, 22 Sep 2011 09:32:04 GMT

Hello Rob,

whilst I was working out your point 3, the head of our ICT-department e-mailed me an old QV-script, in which the idea you mentioned was worked out already. Yesterday he said he couldn't find it, but he eventually did.

So the idea you mentioned was correct indeed, thanks for the help on the matter!

Kind regards,

Stefan