Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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.
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
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).
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
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.
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
Thanks for your prompt respond, what i do i mention the expression for where batchid= user selection batch_id
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