KPI

[vikey]

How to find out the KPI value as per the below query:

SELECT COUNT(*) FROM "XYZ" WHERE Joining Status = ('Not Joined') AND BATCH_ID = (select max(BATCHID) from "ABC") ;

Script:

LOAD

BATCH_ID,

CREATE_TIMESTAMP;

FROM "abc”;

LOAD

BATCHID,

                Emp id,

                Joining Status,

FROM “xyz”;

Sample data :

ABC table:

Batch_ID	Create_TimeStamp
1	20-JAN-22 03.45.44.000000000 PM
2	03-FEB-22 01.54.29.000000000 PM

xyz table:

BatchID	emp id	status
1	460	Joined
1	461	not Joined
2	462	not Joined
2	463	yet to join
2	464	not Joined
1	465	yet to join

[SBDataspark]

Hi there,

Thank you for the sample data to test with.
Is it correct that "Batch_ID" in table ABC is namen differently than "BatchID" in table XYZ?

If that's a mistake and both have the same column name, let's say "BatchID" than this expression might fit your needs:

=Count({<status={'not Joined'},BatchID={"=$(=Max(BatchID))"}>} BatchID)

If they're not the same and you need a relationship between them you should create that in your data model by naming them the same so you can use the above expression.

Or create some kind of link table between them so you can change the expression to:

=Count({<status={'not Joined'},BatchID={"=$(=Max(Batch_ID))"}>} BatchID)

Hopefully this helps you a bit further.

Kind regards,

Sebb

[vikey]

Thanks for your response,  the above expression works for me.

as you asked for the below question.

Q: Is it correct that "Batch_ID" in table ABC is namen differently than "BatchID" in table XYZ?

A: Both  have different values.

[SBDataspark]

Hi,

Good to hear that the expression works for you!
These types of questions help me sharpen my Qlik skills as well...😉

Kind regards,

Sebb

[vikey]

Hi,

In the script, i need to add one more table..

asd table:

BatchID	emp id	status
1	460	Exit
1	461	Exit
2	462	Migrated
2	463	Migrated
2	464	Migrated
1	465	Exit

As i  need to create Bar chart , so i need count of status from xyz table and asd table where batchid= max(batchid). 

[SBDataspark]

Hi Vikey,

If I'm understanding your requirements than this can be done with the following additional load script:

// start Initial load script
[ABC]:
Load * Inline
[BatchID,Create_TimeStamp
1,20-JAN-22 03.45.44.000000000 PM
2,03-FEB-22 01.54.29.000000000 PM
];

[XYZ]:
Load * Inline
[BatchID,emp id,status,SourceTable
1,460,Joined,xyz
1,461,not Joined,xyz
2,462,not Joined,xyz
2,463,yet to join,xyz
2,464,not Joined,xyz
1,465,yet to join,xyz
];
// end initial load script

// start additional load script

// If [XYZ] and [ASD] have the same columns and are loaded right after each other you can do this
Load * Inline
[BatchID,emp id,status,SourceTable
1,460,Exit,asd
1,461,Exit,asd
2,462,Migrated,asd
2,463,Migrated,asd
2,464,Migrated,asd
1,465,Exit,asd
];

//If [XYZ] and [ASD] are loaded in different parts of your load script you can do this
// Join([XYZ])
// Load * etc.

// Both of these will result in a combined table [XYZ]

With the following measurement =Count({<BatchID={"=$(=Max(BatchID))"}>} BatchID) you can create this bar chart:

Kind regards,

Sebb

[vikey]

Thanks for the suggestion, i have one more Query, can you please help me on it.

As i  need to create pie chart , so i need status of count from xyz table and asd table where batchid= user selection batch_id from ABC table.

[SBDataspark]

Hi Vikey,

At the moment our Qlik Sense Saas environment on the EU cloud has some problems editing apps...😉

Can you copy the bar chart, than drag and drop a pie chart onto the copy of your bar chart and choose "convert to pie chart"?

Kind regards,

Sebb

[vikey]

Thanks for your prompt respond, what i do i mention the expression for where batchid= user selection batch_id 

[SBDataspark]

Hi Vikey,

I'm still struggling with the fact that BatchID and Batch_ID are related but named differently.
I guess it makes it easier if you name these two columns the same in your data model or create some sort of linked table to relate these two.

Is this what you're looking for?

Kind regards,

Sebb