filter rows per change date

Analytics
Enthusiast

filter rows per change date

ID of placebrandbegin dateRevenue
1ABC1/1/201847632
1ABC2/3/201832736
1ADG3/2/201832323
1ADG4/5/201832424
1FGH4/5/201823273

How to filter for rows with begin dates which correspond to brand change in second column? These would be rows 1,3,5.


Accepted Solutions
Senior Apprentice

Re: filter rows per change date

Hi,

 

The following should work - provided I've understood your dates correctly. I'm assuming the dates shown below are in US format (i..e m/d/yyyy). if not the following probably is not quite correct.

CREATE SET VOLATILE TABLE vt1
(ID_OF_place	INTEGER
,brand CHAR(3)
,BEGIN_DATE	DATE
,Revenue INTEGER)
PRIMARY INDEX(id_of_place)
ON COMMIT PRESERVE ROWS;

DELETE FROM vt1;

INSERT INTO vt1 VALUES(1,'ABC',DATE '2018-01-01',47632);
INSERT INTO vt1 VALUES(1,'ABC',DATE '2018-02-03',32736);
INSERT INTO vt1 VALUES(1,'ADG',DATE '2018-03-02',32323);
INSERT INTO vt1 VALUES(1,'ADG',DATE '2018-05-04',32424);
INSERT INTO vt1 VALUES(1,'FGH',DATE '2018-05-04',23273);

SELECT vt1.*
   ,MIN(brand) OVER(PARTITION BY id_of_place ORDER BY begin_date,brand ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_brand
FROM vt1
QUALIFY (brand <> prev_brand) OR (prev_brand IS NULL)
ORDER BY 1,2,3;

The above data and query gives the following results (dates are shown in UK format - dd/mm/yyyy):

ID_OF_place	brand	BEGIN_DATE	Revenue	prev_brand
1	        ABC	01/01/2018	47,632	?
1	        ADG	02/03/2018	32,323	ABC
1	        FGH	04/05/2018	23,273	ADG

HTH

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
10 REPLIES
Senior Apprentice

Re: filter rows per change date

Hi,

 

The following should work - provided I've understood your dates correctly. I'm assuming the dates shown below are in US format (i..e m/d/yyyy). if not the following probably is not quite correct.

CREATE SET VOLATILE TABLE vt1
(ID_OF_place	INTEGER
,brand CHAR(3)
,BEGIN_DATE	DATE
,Revenue INTEGER)
PRIMARY INDEX(id_of_place)
ON COMMIT PRESERVE ROWS;

DELETE FROM vt1;

INSERT INTO vt1 VALUES(1,'ABC',DATE '2018-01-01',47632);
INSERT INTO vt1 VALUES(1,'ABC',DATE '2018-02-03',32736);
INSERT INTO vt1 VALUES(1,'ADG',DATE '2018-03-02',32323);
INSERT INTO vt1 VALUES(1,'ADG',DATE '2018-05-04',32424);
INSERT INTO vt1 VALUES(1,'FGH',DATE '2018-05-04',23273);

SELECT vt1.*
   ,MIN(brand) OVER(PARTITION BY id_of_place ORDER BY begin_date,brand ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_brand
FROM vt1
QUALIFY (brand <> prev_brand) OR (prev_brand IS NULL)
ORDER BY 1,2,3;

The above data and query gives the following results (dates are shown in UK format - dd/mm/yyyy):

ID_OF_place	brand	BEGIN_DATE	Revenue	prev_brand
1	        ABC	01/01/2018	47,632	?
1	        ADG	02/03/2018	32,323	ABC
1	        FGH	04/05/2018	23,273	ADG

HTH

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: filter rows per change date

Hello,

 

I put something like this

 

SyntaxEditor Code Snippet

SELECT 
   MIN(p.BRAND_ID) OVER(PARTITION BY p.EXPE_PROPERTY_ID
   ORDER BY p.ROW_EFF_BEGIN_DATETM,p.EXPE_PROPERTY_ID,p.BRAND_ID
 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_brand
FROM P_DM_LODG.HIST_PROPERTY p
QUALIFY (p.BRAND_ID <> prev_brand) OR (prev_brand IS NULL)

 but results looks strange

 

1 ?

2 0

3 ?

4 17

 

Highlighted
Senior Apprentice

Re: filter rows per change date

Hi,

 

It is difficult to work out what is happening because you have provided very little information.

 

Your result set appears to show two columns, but your SELECT only has one column (prev_brand) in the select list. I assume that the first column (values 1-4) is the row number in the result set. If your select list is meant to be only the 'prev_brand' and no 'key value' then how is that going to help your processing?

 

Also, the ORDER BY clause doesn't need "p.EXPE_PROPERTY_ID" UNLESS you are using this to order the final result set.

 

If you can provide some more information (input data values?) maybe we can work out what is happening.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: filter rows per change date

Now it actually looks fine, without rows with "?"

 

So I obtain:

ID     Date           Brand ID  PrevID

44   1/4/2017       0               57

44    2/15/2017    3967          0

59    4/4/2018       372          3256

91     2/3/2017      0               2022

91      3/3/2017      2650        0

 

Now the question remains, how to pull out unique ID numbers and LEFT JOIN it with another SELECT clause which contains financial data for a few years (this one is too big to run - spoolspace, which is why I pull now those unique IDs.

 

Thank you for support!

Senior Apprentice

Re: filter rows per change date

Hi,

 

Ok, two stages to this.

 

First, you say that you want "unique ID numbers". What is your required logic if you have multiple rows with the same ID value? When you've told us that we might be able to help. Do you want the first row for an ID? And if so 'first row' in what sequence? (presumably date).

 

Once you've decided / coded that, a left join to another select should be straight forward in terms of coding. I would code this query as a derived table, and add it into the other select using the appropriate join condition(s).

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: filter rows per change date

This could also suffice( for the first query result that you wanted):

sel * from table qualify row_number() over ( PARTITION by brand ORDER BY BEGIN_DATE )=1

Enthusiast

Re: filter rows per change date

Hello again,

I am close to a perfect database :)

 

Final stage, is to add the flad to those IDs which appear more than once, and mark with TRUE

 

EXPE_PROPERTY_IDLODG_PROPERTY_KEYprev_brandMULTIPLE ID
1240,7460FALSE
8240,7490FALSE
16240,7520TRUE
16240,752117TRUE

 

SELECT
SyntaxEditor Code Snippet
        ,count(p.EXPE_PROPERTY_ID) over ( 
        PARTITION by BRAND_ID 
        ORDER BY ROW_EFF_BEGIN_DATETM  ) as "1"        ,MIN(p.BRAND_ID) OVER(
        PARTITION BY p.EXPE_PROPERTY_ID 
        ORDER BY p.BRAND_NAME ROWS BETWEEN 1 PRECEDING 
            AND 1 PRECEDING) AS prev_brand
        QUALIFY (p.BRAND_ID <> prev_brand) 
            AND (prev_brand IS NOT NULL)
        
        FROM...
 

Count() over ... doesn't help....

 

Do you have any ideas?

 

 

Senior Apprentice

Re: filter rows per change date

Hi,

 

Looking at the snippet of data that you've provided, you need something like:

SELECT *
   ,(CASE
     WHEN COUNT(*) OVER(PARTITION BY expe_property_id) > 1 THEN 'TRUE'
     ELSE 'FALSE'
     END) AS MULTIPLE_ID
FROM table;

HTH

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: filter rows per change date

It will always give the TRUTH as prev_brand already filters some data....