topic Sum 2 fields to create an option in another !! in QlikView

Sum 2 fields to create an option in another !!

Fri, 17 Jun 2011 12:23:54 GMT

Hi Guys,

I have only the personal edition so I can not post an example of my QV doc, I will try my bestest to describe my issue as clear as possible.

I have a QV doc loaded from numerous Excel files!

One of these files is called Accounts; there are 2 particular fields in this report that I am concerned with, [Number of Structured] and [Number of Unstructured].

Now I have a second excel sheet called Customers, one of the fields is called [type of Query],

What I wish is that ,in the script I sum the 2 fields from accounts and load it as a result ( Auto Confos!!) in Customers, this is what I have tried and although it does change my results , it does not change it correctly.

Please ignor the From part of the load I took it out because it was like 5 lines long

Hope someone can help

Thanks

CONCATENATE (MasterTable)

LOAD

Date(Date) as Date,

Month(Date(Date)) as Month,

Week(Date(Date)) as Week,

Year(Date(Date)) as Year,

([Number of Structured]+ [Number of Unstructured])='Auto Confos' as [Type of Query],

'Customer Service' as Activity,

'John' as [Assistant Manager]

FROM

example.MASTER LOG.xls]

Where Date>'06/03/2011' and Date<today();

Sum 2 fields to create an option in another !!

Fri, 17 Jun 2011 12:40:29 GMT

Blossomthe2nd wrote:
What I wish is that ,in the script I sum the 2 fields from accounts and load it as a result ( Auto Confos!!) in Customers,

What is this means,? You are storingas Auto Confos in customer table?

Sorry, didnot get your problem. Could you please post a sample application and explain your requirement (people who has license key would be able to open any application, so pl dont mind to post your sample application)

-Sridhar

Sum 2 fields to create an option in another !!

Fri, 17 Jun 2011 13:01:46 GMT

Hi Sridhar

In the our Job , everytime a structured or unstructured event happens we also have to complete a "Auto Confo", as our excel files are manually updated I wish that every time we log a Structured or unstructured QV knows that we wuld have to do a Auto Confo, so our guys would not have to log an Auto Confo on customer.

The account spreadsheet has a 2 columns Called Structured and another called Unstructured.

These have numeric values , this is loaded into QV as activity Rollover.

I have a second spreadsheet and its activity is customer. One of the columns on this is Type of Query. So I wish that the sum of Struct and Unstruct is added to the number column in Customer and the column Type of query recognises it as Auto Confo.

The above are the 2 spread sheets fso for example on the 7 March I would like QV to count 87 Types of query as Auto Confo,

( 87 being the sum of 38 and 49 Struct and Unstruct)

Can this be done ?

Sum 2 fields to create an option in another !!

chriscammers — Fri, 17 Jun 2011 13:02:14 GMT

The field you have after Year would end up being a True False value and it's name would be "Type Of Query" Based on what your wrote I dont this that is what you want.

this would work...

([Number of Structured]+ [Number of Unstructured]) as [Auto Confos],

[Type of Query]

but I am surprised that what you have even executes

Sum 2 fields to create an option in another !!

Fri, 17 Jun 2011 13:22:15 GMT

Hi Chris

I think the satement as you provided will change the sum to a field called Auro Confos,

However on reading your post a little light bulb went off,

If I call the field Number and then the result of type of Query as Auto Confo it should work

Thanks

CONCATENATE (MasterTable)

LOAD

Date(Date) as Date,

Month(Date(Date)) as Month,

Week(Date(Date)) as Week,

Year(Date(Date)) as Year,

([No. of Unstructured Rollovers]+[No. of Structured Rollovers])as Number,

'Auto Confirmations' as [Type of Query],

'Customer Service' as Activity,

'John' as [Assistant Manager]

FROM