Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
qlik5
Contributor II
Contributor II
‎2021-07-19 05:17 AM

Group By and Previous Function

Hello,

If have a Table with 3 columns:

IDDate1Date2

1

13.01.202116.01.2021
213.01.202118.01.2021
218.01.202125.01.2021
302.05.202117.05.2021
318.05.202119.06.2021
413.01.202118.01.2021
416.06.202117.07.2021
517.12.202008.01.2021
518.02.202103.03.2021
504.03.202115.05.2021
515.05.202117.05.2021
516.06.202108.07.2021

 

As you can see, I have already sorted the data both by ID and by Date1:

Temp2:
Load
ID,
Date1,
Date2

Resident Temp1
Group By ID, Date1
;

I would like to check for each ID (which occurs more than 1 time) whether the Date1 matches the previous Date2, or is only a maximum of 3 days apart. If that is the case, I would like to flag the most recent row with a 1. If the ID occurs only once, or the distance between Date1 and the previous Date2 is greater than 3 days, I would also like to flag the rows with a 1.

Edit: I want to check for each ID (which occurs more than 1 time) if the Date2 matches the following Date1, or is only a maximum of 3 days apart. If this is the case, I want to mark the first line with a 0, the second with a 1. If the ID occurs only once or the distance between Date2 and the following Date1 is greater than 3 days, I want to mark the lines with a 1 as well.

 

This would be the target table:

IDDate1Date2Flag

1

13.01.202116.01.20211
213.01.202118.01.20210
218.01.202125.01.20211
302.05.202117.05.20210
318.05.202119.06.20211
413.01.202118.01.20211
416.06.202117.07.20211
517.12.202008.01.20211
518.02.202103.03.20210
504.03.202115.05.20210
515.05.202117.05.20211
516.06.202108.07.20211

 

Can anyone help me?

Labels (4)
Labels
1,664 Views
0 Likes
9 Replies
Kushal_Chawda
MVP
MVP
‎2021-07-19 07:00 AM

@qlik5  don't quite understand. How below Flag to 1? As you said where ID is more than 1, Date 1 & Date 2 max of 3 days apart, but it is more than 3 days apart

218.01.202125.01.20211
1,612 Views
0 Likes
qlik5
Contributor II
Contributor II
‎2021-07-19 07:20 AM
Author

In response to Kushal_Chawda

Thanks for your answer!

I try to explain again in more detail in which cases the flag should be set to 1:

 

Imagine that Date1 represents the purchase date and Date2 the sales date. If previous(Date2) and Date1 are close to each other (i.e. not more than 3 days apart), this indicates internal sales. I do not want to take these into account, so the flag is set to 0 here.

If the ID occurs only once, I set the flag to 0 (ID=1).

If ID=2, we see that there was first an internal sale (flag=0), with the sale date on 01/18. Then it was sold externally (on 01/25), so here the flag is set to 1.

For ID=3 it is similar to ID=2, except that here the first sale date (17.05.2021) is one day before the purchase date (18.05.2021). Due to time delayed system updates up to 3 days difference can take place, nevertheless it is an internal sale.

For ID=4, the goods were initially purchased on 01/13/21 to be sold on 01/18/2021. At a later time (>3 days) the goods were bought again by the external customer, here on 16.06.2021 and finally sold again on 17.07.2021. Since both sales are external sales, the flag is set to 1 in each case.

At ID=5 I have compiled the different scenarios:
On 12/17/2020 the goods were first bought and then sold on 01/08/2021 (flag=1). On 18.02.2021 the goods are bought externally again and then sold internally twice (on 03.03.2021 and on 15.05.2021, flag=0 in each case). On 15.05.2021 the goods are finally sold externally (flag=1). On 06/16/2021 the goods are again purchased externally (flag=1).

I hope the explanations help. In principle, I would like to identify internal sales. And internal sales can be recognized by the fact that the difference of previous(Date2) and Date1 is less than 3 days, with the same ID of course.

1,593 Views
0 Likes
Kushal_Chawda
MVP
MVP
‎2021-07-19 08:00 AM

@qlik5  sorry but I am still confused. For internal sale do you need to flag =0? If so how would you flag =0 for below as you said internal sale logic need to be checked for same ID, but this is the first record of ID=2

213.01.202118.01.20210
1,571 Views
0 Likes
qlik5
Contributor II
Contributor II
‎2021-07-19 08:44 AM
Author

In response to Kushal_Chawda

Exactly, for internal sales I set the flag to 0.

@Kushal_Chawda 

For ID=2 I have two lines:
In the first line I buy the item on 13.01.2021 and sell it internally on 18.01.2021. That it is an internal sale can be seen from the purchase date in the second line, this is on 18.01.2021.

So yes- I look at a new ID first to see if the ID occurs only once. If yes (as in ID=1), the case is simple and I set the flag to 1. If no, I look at the second entry. If the purchase date from the second entry matches the sales date from the first entry, I set the flag in the first row to 0.
Then I look if there is another entry with the same ID. If not, the flag of the second entry is set to 1 - just like ID=2. If there are more entries with the same ID - as with ID=5 - I look at the next entry and compare the purchase date with the previous invoice date.

213.01.202118.01.20210
218.01.202125.01.20211
1,549 Views
0 Likes
qlik5
Contributor II
Contributor II
‎2021-07-19 08:47 AM
Author

In response to qlik5

@Kushal_Chawda 

This is an external purchase (ID=2 appears here for the first time), but an internal sale - as you can see in the following line. I don't care if it was an internal or external purchase, I just want to know if it was an internal or external sale.

213.01.202118.01.20210
1,532 Views
0 Likes
qlik5
Contributor II
Contributor II
‎2021-07-19 09:07 AM
Author

In response to qlik5
IDDate1Date2FlagComment

1

13.01.202116.01.20211There is only one entry with the ID=1, therefore the flag=1
213.01.202118.01.20210There are two entries with ID=2. I compare the Date2 with the Date1 of the following row. If they match (+3 days), it is an internal sale and I set the flag to 0.
218.01.202125.01.20211This is the last entry with ID=2, so the flag is set to 1.
302.05.202117.05.20210At ID=3 there are again two entries. The difference between Date2 and the following Date1 is <= 3 days, so it is again an internal sale and the flag is set to 0.
318.05.202119.06.20211This is the last entry with ID=3, so the flag is set to 1.
413.01.202118.01.20211If ID=4, there are more than 3 days between Date2 and the following Date1. Therefore the flag is set to 1. (num(16.06.2021)-num(18.01.2021))>3
416.06.202117.07.20211This is the last entry with ID=4 so the flag is set to 1.
517.12.202008.01.20211external purchase (num(18.02.2021)-num(08.01.2021))>3
518.02.202103.03.20210internal purchase (num(04.03.2021)-num(03.03.2021))<=3
504.03.202115.05.20210internal purchase (num(15.05.2021)-num(15.05.2021))<=3
515.05.202117.05.20211external purchase num(16.06.2021)-num(17.05.2021))>3
516.06.202108.07.20211last entry with ID=5
1,511 Views
qlik5
Contributor II
Contributor II
‎2021-07-20 02:15 AM
Author

In response to qlik5

I guess if someone could show me how to compare Date2 of the current row with Date1 of the following row, that would be quite sufficient for me as a first step.

1,433 Views
0 Likes
Kushal_Chawda
MVP
MVP
‎2021-07-20 05:30 AM

@qlik5  let me work on this

1,402 Views
0 Likes
qlik5
Contributor II
Contributor II
‎2021-07-20 06:34 AM
Author

@Kushal_ChawdaThank you very much for your effort! I have just - finally - actually figured it out myself:

 

Temp3:
NoConcatenate
Load


*,
If(ID=previous(ID),
if((previous(Date1) - Date2)<4, 0,1),1) as Flag_externalPurchase


Resident Temp2
order by ID, Date1 desc, Date2 desc;
;
Drop Table Temp2;

 

 

The trick was to sort the date fields in descending order, then you can use the previous() function.

1,370 Views
0 Likes