Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
in the below table i want to remove the records highlighted in RED. as it is the sum of records above it.
How to remove this record? excel attached for Ref:
Product.productGroup | Clicks |
AND(PRODUCT_TYPE_LEVEL_1 = "cuidado personal",CUSTOM_LABEL_3 = "afeitadoras aquatouch",PRODUCT_ID = "s5050/64") | 865 |
AND(PRODUCT_TYPE_LEVEL_1 = "cuidado personal",CUSTOM_LABEL_3 = "afeitadoras aquatouch",PRODUCT_ID = "s5400/06") | 233 |
AND(PRODUCT_TYPE_LEVEL_1 = "cuidado personal",CUSTOM_LABEL_3 = "afeitadoras aquatouch",PRODUCT_ID = "s5420/06") | 615 |
AND(PRODUCT_TYPE_LEVEL_1 = "cuidado personal",CUSTOM_LABEL_3 = "afeitadoras aquatouch") | 1713 |
AND(CUSTOM_LABEL_2 = "bodygroom",CUSTOM_LABEL_3 = "dauerhafte haarentfernung") | 488 |
AND(CUSTOM_LABEL_2 = "bodygroom",CUSTOM_LABEL_3 = "rasierer") | 269 |
AND(CUSTOM_LABEL_2 = "bodygroom",CUSTOM_LABEL_3 = "schneiden und rasieren") | 649 |
AND(CUSTOM_LABEL_2 = "bodygroom") | 1406 |
For your data you have to use at least also the date field. Possibly other fields as well, for example:
t:
load
*,
Product.Date & '|' & [Product.Paid Search Ad Group] & '|' & left(Product.productGroup,len(Product.productGroup)-1) as ppg
from [.\Search1.qvd](qvd);
Final:
load
*
;//where idx<>1;
load
*,
Index(Previous(ppg),ppg) as idx
Resident t Order by ppg desc;
//drop fields ppg,idx;
drop table t;
For example:
t:
load Product.productGroup,
Clicks,
left(Product.productGroup,len(Product.productGroup)-1) as ppg //delete closing bracket
from [.\duplicate.xlsx](ooxml, embedded labels, table is Sheet1);
Final:
load
*
where idx<>1;
load
*,
Index(Previous(ppg),ppg) as idx
Resident t Order by ppg desc;
drop fields ppg,idx;
drop table t;
Hi
The actual file structure is attached. can you help with the solution. i tried with above solution but somehow it didnt work on the actual file structure.
It works. Look at the attachment. In the table all lines with idx=1 will removed. I think that is what you want.
Hi ,
the above solution is the exact output which i want.
Im using the same code as yours but somehow when i select idx 0 there are very few click numbers.
attached is reference file and qvd
Kindly look into this
For your data you have to use at least also the date field. Possibly other fields as well, for example:
t:
load
*,
Product.Date & '|' & [Product.Paid Search Ad Group] & '|' & left(Product.productGroup,len(Product.productGroup)-1) as ppg
from [.\Search1.qvd](qvd);
Final:
load
*
;//where idx<>1;
load
*,
Index(Previous(ppg),ppg) as idx
Resident t Order by ppg desc;
//drop fields ppg,idx;
drop table t;