Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to create a column in a table that would identify the first date within client that have multiple entries.
This is what the date looks below.
Client Id | Entry Exit Provider Name | Entry Date | Exit Date Adjusted | Test |
758 | LSS - FM Faith on 6th | 3/22/2018 | 4/5/2018 | 20180322 |
758 | LSS - FM Faith on 6th | 4/26/2018 | 6/4/2018 | 20180426 |
2134 | LSS - FM Faith on 6th | 3/30/2018 | 6/15/2018 | 20180330 |
2517 | LSS - FM Faith on 6th | 3/9/2018 | 5/9/2018 | 20180309 |
4586 | LSS - FM Nancy's Place | 2/10/2018 | 6/18/2018 | 20180210 |
4587 | LSS - FM Nancy's Place | 2/19/2018 | 5/4/2018 | 20180219 |
5128 | LSS - FM Nancy's Place | 5/19/2018 | 6/4/2018 | 20180519 |
My desire result would be:
Client Id | Entry Exit Provider Name | Entry Date | Exit Date Adjusted | Test |
758 | LSS - FM Faith on 6th | 3/22/2018 | 4/5/2018 | Fist |
758 | LSS - FM Faith on 6th | 4/26/2018 | 6/4/2018 | |
2134 | LSS - FM Faith on 6th | 3/30/2018 | 6/15/2018 | First |
2517 | LSS - FM Faith on 6th | 3/9/2018 | 5/9/2018 | First |
4586 | LSS - FM Nancy's Place | 2/10/2018 | 6/18/2018 | First |
4586 | LSS - FM Nancy's Place | 2/19/2018 | 5/4/2018 | |
5128 | LSS - FM Nancy's Place | 5/19/2018 | 6/4/2018 | First |
I've seen few of examples but I'm still learning the syntax structure of Qlik and it's hard to apply wihtou knowing what thte formula's are doing.
Thanks.
So, you need this on the front end of the app? You can try this
If([Entry Date] = Min(TOTAL <[Client Id]> [Entry Date]), 'First')
Try this
Table:
LOAD * INLINE [
Client Id, Entry Exit Provider Name, Entry Date, Exit Date Adjusted
758, LSS - FM Faith on 6th, 3/22/2018, 4/5/2018
758, LSS - FM Faith on 6th, 4/26/2018, 6/4/2018
2134, LSS - FM Faith on 6th, 3/30/2018, 6/15/2018
2517, LSS - FM Faith on 6th, 3/9/2018, 5/9/2018
4586, "LSS - FM Nancy's Place", 2/10/2018, 6/18/2018
4586, "LSS - FM Nancy's Place", 2/19/2018, 5/4/2018
5128, "LSS - FM Nancy's Place", 5/19/2018, 6/4/2018
];
Left Join (Table)
LOAD [Client Id],
Min([Entry Date]) as [Entry Date],
'First' as Test
Resident Table
Group By [Client Id];
I'm using Qlik Sense, I'm unable to use SQL or load data.
Qlik Sense does have a load script, what do you mean when you say that you are able to use load data? You don't have privileges to make script modifications?
correct, I'm working from a 3rd party vendor that slowly switching over to Qlik.
So, you need this on the front end of the app? You can try this
If([Entry Date] = Min(TOTAL <[Client Id]> [Entry Date]), 'First')
Interesting, what does the "total" in the formula accomplish?
Look here
Thank you.
Last question, can a formula like "If([Entry Date] = Min(TOTAL <[Client Id]> [Entry Date]), 'First')" be used in an set analysis expression?
What exactly is the context? Difficult to know without knowing what you are trying to do...