Apply filters to the data only after the server updates

[JonesBeach]

Greetings all,

Thanks to whoever comes forth for help! 

I am fairly new to qliksense and having hard time understanding it. I have experience with Power Bi but my company does not uses that as an authorized platform for dashboard development. 

I have a data set and I want it to be filtered for certain products only for each SKU number. Is there a way to have those product filters applied only when the server updates the data at 2:00am each day and when that happen could those be rows be highlighted that fulfills the filter requirements. Thanks again for help!

Basically after the update which ever SKU number has a Product GNT933, GNT222 or GNT456 associated to should get highlighted. 

I am attaching a pseudo data file for you to work with 🙂 

[rubenmarin · Accepted solution]

Hi, one thing is filtered, when you only see the filtered data, and another one is highlighted, when you see all data, without filters, and added some background color to those products.

To apply filters you can create a bookmark: select the values, crete the bookmark using the button at left of the sheet selector  and right click on it to make the default bookmark.

To highlight there could be different strategies. You can add those values to an inline table and flag the records that match the product:

LOAD * Inline [
ProductHL
GNT933
GNT222
GNT456
];

Then load SKU table and process data to flag records that have to be highlighted:

Left Join (SKU)
LOAD
	Products,
    Max(flagHL) as flagHL
Group By Products;
LOAD
	Products,
    Product,
    If(Exists('ProductHL',Product),1,0) as flagHL;
LOAD
 Products,
 trim(Subfield(Products,',')) as Product
Resident SKU
;

After that you only need to use this flag to apply the background color:

If(flagHL, Green())

BTW: in the excel data why aren't SKU-23, SKU-25 and SKU-27 HL? they have GNT933 or GNT222 products

[JonesBeach]

Hi but this is not taking care of the SKU numbers modified later. Like for SKU27 lets say on 10/07/2021 there was only GNT121 associated to it but after sql data update product GNT222 also got associated to SKU27. 

For this later modification on 10/8/2021 i wanted to highlight that or show that this got later updated. 

SN: you are right, the other SKU's with products of interest associated to them should have also been highlighted.

Thanks a lot for coming forth for the rescue, sir!

Have a great rest of the day!

regards,

JonesBeach 

[rubenmarin]

Hi, I'm not sure to understand... Higlighted rows depends on the values found on the products Column. SKU27 on 10/07/2021 will not be highligted because it's Products values won't contain any of the the products to be highlighted. The one on 10/8/2021 will be highligted.

... or you are asking to flag all SKU that any of it's occurrences has any of the products? To do that you only need to change 'Products' with 'SKU#' to relate flagHL with every SKU

[JonesBeach]

In other words lets say I only want to highlight the SKU that initially had no products of interest associated to them but after the data update it got any of the 3  or all of the 3 products of interest associated to it.

I am sorry for misunderstanding but if you could clear that out then that would be awesome. 

Thanks again!

[rubenmarin]

But if a SKU initially has any of the products you want to highlight it? Or only the ones that at first doesn't has any at later is has any?

And if a SKU on one date doesn't has any prodcut, the it has a update with another product that also hasn't to be highlighted, and later another update more than add an highlighted product... wich dates has to be higlighted? all or only some?

And lastly, if a SKU and date has an highlighted product and with a later update the SKU only has products no thiglighted, Any of these SKUs has to be highlighted?

[JonesBeach]

Hi Ruben, again thanks for looking into this!

So I want to highlight only those SKU's that have our required products either added to them or taken away from them on a later date. 

So the criteria for highlighting would be:

If there is a SKU that has  Product GNT933, GNT222 or GNT456 associated to it initially but then after the data update all of those products gets dropped and now I want it to be highlighted in Red. 

OR

If there is a SKU with none of Product GNT933, GNT222 or GNT456 associated to it initially but then after the data update all of those products gets added and now I want it to be highlighted in green. 

I am sorry I was originally intending to get a code that highlights the rows after the update and then I was thinking of modifying further to apply to what I actually want but I think that creates confusion so this is what I am ultimately looking for.

Also it would be awesome if I can add a column showing the date those SKU's were modified. 

Thanks so much for the help again, this means a lot!

regards!

[rubenmarin]

Hi, from the initial proposal, where you have each row identified you can do a new process of that table.

Loading date using "Order by SKU#, Date" you can use peek to chek if the previous row has the falg=0 and this row has the flag=1, this will meant that an higlighted product has added.

LOAD ...,
  SKU#,
  flagHL,
  If(Peek(SKU#)=SKU# and Peek(flagHL)=0 and flagHL=1, 1 , 0) as flagAdded,
  If(Peek(SKU#)=SKU# and Peek(flagHL)=0 and flagHL=1, Date) as DateAdded,
  If(Peek(SKU#)=SKU# and Peek(flagHL)=1 and flagHL=0, 1 , 0) as flagRemoved,
  If(Peek(SKU#)=SKU# and Peek(flagHL)=1 and flagHL=0, Date) as DateRemoved,
Resident [PreviousTableName]
Order by SKU#, Date;

[JonesBeach]

Hi Ruben, 

I have tried the code up there but It is not flagging the change.  Its not showing 1 for the SKUs in the flagAdded column or flagRemoved. This is the last part I am stuck on. can anyone please guide. Thanks!

[rubenmarin]

Hi, I tested with this script and it flgas the added and removed product on SKU27:

HLProducts:
LOAD * Inline [
ProductHL
GNT933
GNT222
GNT456
];

SKU:
LOAd * Inline [
Date;	SKU#;	Products
01/10/2021;	SKU-21;	GNT456, GNT545, GNT933
02/10/2021;	SKU-22;	GNT457, GNT222
03/10/2021;	SKU-23;	GNT458, GNT933
04/10/2021;	SKU-24;	GNT458
05/10/2021;	SKU-25;	GNT222
06/10/2021;	SKU-26;	GNT111, GNT456
06/10/2021;	SKU-27;	GNT121
07/10/2021;	SKU-27;	GNT121, GNT222
08/10/2021;	SKU-27;	GNT121
] (delimiter is ';');

Left Join (SKU)
LOAD
	Products,
    Max(flagHL) as flagHL
Group By Products;
LOAD
	Products,
    Product,
    If(Exists('ProductHL',Product),1,0) as flagHL;
LOAD
 Products,
 trim(Subfield(Products,',')) as Product
Resident SKU
;

EndTable:
LOAD 
	Date,
    Products,
	SKU#,
	flagHL,
	If(Peek(SKU#)=SKU# and Peek(flagHL)=0 and flagHL=1, 1 , 0) as flagAdded,
	If(Peek(SKU#)=SKU# and Peek(flagHL)=0 and flagHL=1, Date) as DateAdded,
	If(Peek(SKU#)=SKU# and Peek(flagHL)=1 and flagHL=0, 1 , 0) as flagRemoved,
	If(Peek(SKU#)=SKU# and Peek(flagHL)=1 and flagHL=0, Date) as DateRemoved
Resident SKU
Order by SKU#, Date;

DROP table SKU;

In case the first date by SKU already has a HL product na dwou want to count the first one as added the condition can be:

If((Peek(SKU#)=SKU# and Peek(flagHL)=0 and flagHL=1) or (Peek(SKU#)<>SKU# and flagHL=1), 1 , 0) as flagAdded,
	If((Peek(SKU#)=SKU# and Peek(flagHL)=0 and flagHL=1) or (Peek(SKU#)<>SKU# and flagHL=1), Date) as DateAdded,