Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have this SQL shown below. Only problem is that there is a problem with 2 of the days with 2 specific style.
Is there a way to remove style numbers 12170151 and 12138940 for the days 23-12-2019 and 24-12-2019?
SELECT TTL_CALC_COST,
STOCK_AGE,
COLLECTION_NAME,
COLLECTION_TERM,
STOCK_DATE,
STOCK_COUNTRY_NAME,
QUANTITY_TYPE,
WAREHOUSE_NAME,
SUPPLY,
ASSRTMNT_TYPE_NAME,
QUANTITY,
TTL_WHS_EUR,
BRAND_NAME,
STYLES_ID,
SALES_TYPE,
COUNTRY_CMP,
STYLE_CATEGORY_NAME,
STYLE_SUBCATEGORY_NAME,
STYLE_TYPE,
STYLE_NUMBER,
STYLE_NAME,
COLLECTIONS_ID,
ASSORTMENTS_ID,
ASSORTMENT_NAME,
CUSTOMER_GROUPS_ID,
GROUP_NAME,
SUBDEPARTMENT_ID
FROM DISCOVER.DISC_STOCK
Where SUBDEPARTMENT_ID = '21'
hi @imlaug
Add a conditioanal as below:
SELECT TTL_CALC_COST,
STOCK_AGE,
COLLECTION_NAME,
COLLECTION_TERM,
STOCK_DATE,
STOCK_COUNTRY_NAME,
QUANTITY_TYPE,
WAREHOUSE_NAME,
SUPPLY,
ASSRTMNT_TYPE_NAME,
QUANTITY,
TTL_WHS_EUR,
BRAND_NAME,
STYLES_ID,
SALES_TYPE,
COUNTRY_CMP,
STYLE_CATEGORY_NAME,
STYLE_SUBCATEGORY_NAME,
STYLE_TYPE,
STYLE_NUMBER,
STYLE_NAME,
COLLECTIONS_ID,
ASSORTMENTS_ID,
ASSORTMENT_NAME,
CUSTOMER_GROUPS_ID,
GROUP_NAME,
SUBDEPARTMENT_ID
FROM DISCOVER.DISC_STOCK
Where (SUBDEPARTMENT_ID = '21') AND (STOCK_DATE <> '23-12-2019' OR (STYLE_NUMBER<>12170151 AND STYLE_NUMBER<>12138940))
AND (STOCK_DATE <> '23-12-2019' OR (STYLE_NUMBER<> 12170151 AND STYLE_NUMBER<>12138940))
Regards,
Lin
Create measure
New measure=if(max(Table[datecolumn]) in {"23-12-2019","24-12-2019"} && table[style Number] in {12170151,12138940},1,-1)
then add this measure to visual level filter and set it to 1.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
Hi Pravin Wattamwar,
Thank you for your response, however the struggle isnt to create a measure or an extra column, but to do it via the SQL.
The issue is that i would prefer to have it removed via the SQL, as the SQL is used multiple places.
By doing that i can avoid having to create a measure in every report.
So you need SQL of this?
simply update where clause
where style_number not in (Value1,value2)
and date not in ("value1","value2")
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
But wouldnt that remove the 2 days completely?
I only need to remove the figures for those 2 styles on those 2 days. i still want all other data from those 2 days.
Does it make sense?
then simply create cte
;with cte as(
Select
Case when style_number in (value1,value2) and date in (value1,value2) then null else style_number end as [style number]
from table
)
select * from cte where [style number] is not null
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
It seems more like a solution that i am looking for, however, i am still a rookie at SQLs, so i dont really know how to put it in. Can you show me how to put it in, in relation to the SQL i posted? 🙂
And thank you!
;with cte as(SELECT TTL_CALC_COST,
STOCK_AGE,
COLLECTION_NAME,
COLLECTION_TERM,
STOCK_DATE,
STOCK_COUNTRY_NAME,
QUANTITY_TYPE,
WAREHOUSE_NAME,
SUPPLY,
ASSRTMNT_TYPE_NAME,
QUANTITY,
TTL_WHS_EUR,
BRAND_NAME,
STYLES_ID,
SALES_TYPE,
COUNTRY_CMP,
STYLE_CATEGORY_NAME,
STYLE_SUBCATEGORY_NAME,
STYLE_TYPE,
case when STYLE_NUMBER in (val1,val2) && Date in ("val1","val2") then null else STYLE_NUMBER end as [STYLE_NUM] ,
STYLE_NAME,
COLLECTIONS_ID,
ASSORTMENTS_ID,
ASSORTMENT_NAME,
CUSTOMER_GROUPS_ID,
GROUP_NAME,
SUBDEPARTMENT_ID
FROM DISCOVER.DISC_STOCK
Where SUBDEPARTMENT_ID = '21')
select * from CTE where [style_num] is not null
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |