Creating new columns

[rido1421]

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

[alexandros17]

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

[rido1421]

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

[alexandros17]

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!

[jagan]

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.

[rido1421]

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

[rido1421]

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