Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
rido1421
Creator III
Creator III

Creating new columns

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

6 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

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
Partner - Champion III
Partner - Champion III

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
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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