topic Re: Creating new columns in QlikView

Creating new columns

rido1421 — Tue, 08 Apr 2014 08:55:16 GMT

Please could you assist

I have two tables, each table has a field called GROUPNAME.

[Open Cases]:

SerialNoOPEN,

GROUPNAME,

AGENTNAME,

DATE

[Closed Cases]:

GROUPNAME,

DATE,

SerialNoCLOSED

I need to create a new column using one of the items in the "GROUPNAME" field

I need to count the serial number for all

Load*,

Count(If(match(GROUPNAME,'VAS'),SerialNoOpen)) as VASOPEN

resident [Open Cases]

Group by

SerialNoOPEN,

GROUPNAME,

AGENTNAME,

DATE

I then need to get the Count of the Closed case serial numbers for VAS as well

Re: Creating new columns

alexandros17 — Tue, 08 Apr 2014 08:59:09 GMT

what you have done is correct but you need to have only 1 table:

[Open Cases]:

SerialNoOPEN,

GROUPNAME,

AGENTNAME,

DATE

left join

[Closed Cases]:

GROUPNAME,

DATE,

SerialNoCLOSED

Now continue with your load and you'll get the result desidered

Re: Creating new columns

rido1421 — Tue, 08 Apr 2014 09:12:44 GMT

Thank you Alessandro

If I had 5 tables would the left join still work?

Ridhaa Hendricks

Data Analyst

0842276595

r.hendricks@cellc.co.za

This email and its contents are subject to our email legal notice which can be viewed at http://www.cellc.co.za/dl/cms/downloads/Email_legal_notice.pdf

Re: Creating new columns

alexandros17 — Tue, 08 Apr 2014 09:16:38 GMT

It depends on how are they logically linked. If there is a main table and other tables have attributes linked by a key yhe answer is Yes!

Re: Creating new columns

jagan — Tue, 08 Apr 2014 09:28:35 GMT

Hi,

Try like this

Data:

LOAD

SerialNoOPEN AS SerialNo,

GROUPNAME,

AGENTNAME,

DATE,

'Open' AS Status

FROM OpenCases;

Concatenate(Data)

LOAD

GROUPNAME,

DATE,

SerialNoCLOSED AS SerialNo,

'Closed' AS Status

FROM ClosedCases;

Now use the below expressions

Open Cases:

Count({<GROUPNAME= {'VAS'}, Status = {'Open'}>} SerialNo)

Closed Cases:

Count({<GROUPNAME= {'VAS'}, Status = {'Closed '}>} SerialNo)

Hope this helps you.

Regards,

Jagan.

Re: Creating new columns

rido1421 — Tue, 08 Apr 2014 10:12:08 GMT

Thank you very much!

Ridhaa Hendricks

Data Analyst

0842276595

r.hendricks@cellc.co.za

This email and its contents are subject to our email legal notice which can be viewed at http://www.cellc.co.za/dl/cms/downloads/Email_legal_notice.pdf

Re: Creating new columns

rido1421 — Tue, 08 Apr 2014 10:12:08 GMT

Thank you , this helps alot

Ridhaa Hendricks

Data Analyst

0842276595

r.hendricks@cellc.co.za

This email and its contents are subject to our email legal notice which can be viewed at http://www.cellc.co.za/dl/cms/downloads/Email_legal_notice.pdf