Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Ho to get a particular set of values from another set of values

Avatar

Level 4

Hi Team,

 

I want to filter out some records based on the below conditions.

example:

Input records:

customer idcustomer typeDate
1Full time01-01-2016
1Full time01-01-2018
1Full time03-01-2020
1Part Time04-01-2021
1Part Time01-01-2015
2Full time06-01-2016
2Part Time01-01-2019
3Full time08-01-2016
3Part Time12-01-2016
3Part Time10-01-2017
3Part Time18-01-2021
4Full time12-01-2016

 

Output records expected:

On first run:

customer idcustomer typeDate
1Full time01-01-2016
1Full time01-01-2018
1Full time03-01-2020

 

On second run:

customer idcustomer typeDate
1Part Time04-01-2021
1Part Time01-01-2015

 

On third run:

customer idcustomer typeDate
2Full time06-01-2016

 

Etc

 

So basically i want a filtering technique which will filter out all the records for that particular customer with same customer type.

I tried with deduplication but only the latest one or any random one is coming.

Could someone please help me in this?

 

@Manoj_Kumar_  @Jyoti_Yadav 

 

Thanks &regards

Greeshma

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @Greeshma_Sampath1 ,

 

You can use below logic.

 

1) Use query activity to fetch all the customers. Here your output will have all the records from customer table. Define one variable, : vars.customerId = "1";

2) Add test activity to check if number of records greater than zero.

3) Use 'split' activity. Split all your customers based on different types of customer type. Also customer Id equals to $(vars/@customerId);

Also, add the complimentary branch. This will have all the records other than customer Id 1.

4) Continue with complimentary branch. Use Javascript to increment the vars.customerId variable by 1.

5) Use wait activity. Give wait time of 5 sec. Connect the output branch to Step 2, 'test' activity.

 

Thanks,

Jyoti

View solution in original post

9 Replies

Avatar

Level 5

Have you tried a split on customer type then a dedup on customer id? in the dedup you can use the advanced tab to keep the oldest or newest based on the date.

Avatar

Level 4

Hi @dloyd ,

 

Thanks for the response.

 

Split on a particular customer id will not help is what i thought as the customer ids can be hundreds or thousands so a generic way to filter is what iam looking for.

 

Avatar

Level 5

Split on customer TYPE (not customer ID) is what I was recommending. Based on your data set seems you only want "Full Time"

Avatar

Level 4

So i tried to do split on customer type as "full time" and deduplicate on the customer id and the result was only  record. 

Is there a way to bring all those 3 records as output?

 

Avatar

Level 5

Its not clear what you want. Initially you said "filter out all the records", then you are saying you want to keep the ones that get filtered out.

 

Do you want to aggregate the values? Or do you want to market to one person? What is the result you want?

 

If you want to aggregate check out step 1 here:https://experienceleague.adobe.com/docs/campaign-classic/using/automating-with-workflows/use-cases/d... 

instead of Max(), you can use Count(). That will give you that total count per person, while keeping all the records.

Avatar

Level 4

As shown in the example above.. I want to filter out the set of values based on customer id and customer type.

As you can see in the input values we have around 5 customers with id 1 and amoung them only 3 are having customer type "full time" . So the output or the result i need is these 3 records at a time. After this again the flow should run and pick the customer id 1 with customer type "part time" and give out those 2 records and so on with other customer ids. 

I am hoping the issue is clear now.If not feel free to ask.

Thanks & Regards,

Greeshma 

Avatar

Correct answer by
Community Advisor

Hi @Greeshma_Sampath1 ,

 

You can use below logic.

 

1) Use query activity to fetch all the customers. Here your output will have all the records from customer table. Define one variable, : vars.customerId = "1";

2) Add test activity to check if number of records greater than zero.

3) Use 'split' activity. Split all your customers based on different types of customer type. Also customer Id equals to $(vars/@customerId);

Also, add the complimentary branch. This will have all the records other than customer Id 1.

4) Continue with complimentary branch. Use Javascript to increment the vars.customerId variable by 1.

5) Use wait activity. Give wait time of 5 sec. Connect the output branch to Step 2, 'test' activity.

 

Thanks,

Jyoti

Avatar

Level 4

Hi @Greeshma_Sampath1 ,

If I understand the requirement correctly, then what you would like to have is a looped execution of query with different results at each execution. I think the following structure of workflow might satisfy that requirement.

Krishnanunni_0-1642597099982.png

I have created a file based on your data and loaded using the data loading.
Code inside definition:

var query = xtk.queryDef.create(
  <queryDef schema={vars.targetSchema} operation="select">
    <select>
      <node expr="customer_id"/>
      <node expr="customer_type"/>
    </select>
  </queryDef>
)

var res = query.ExecuteQuery()

var keyValPair = {};
for each (var row in res)  {
  logInfo(row.customer_id);
  logInfo(row.customer_type);
  keyValPair[row.customer_id+row.customer_type] = [row.customer_id, row.customer_type];
}

keys=[];
vals=[];
len = 0;
for (var item in keyValPair) {
     keys.push(item);
     vals.push(keyValPair[item]);
     len++;
}
//logInfo(keys)
instance.vars.combinations = vals;



//loop variable
instance.vars.loop = 0;
instance.vars.loopLength= len;

You may skip data loading as you would have the original data in some schema. So replace the {var.targetSchema} with schema name like "nms:recipient". You might need to add '@' before column names when using schema. 

Code inside loop definition:

instance.vars.cust_id = instance.vars.combinations[instance.vars.loop][0];
instance.vars.cust_type = instance.vars.combinations[instance.vars.loop][1];

In query activity, set the value expression as in below ss

Krishnanunni_1-1642597260461.png

Code in JavaScript code 3

instance.vars.loop++;

Test activity

Krishnanunni_2-1642597311150.png

 

 

Avatar

Administrator

Hi @Greeshma_Sampath1,

Were you able to resolve this query with the help of any of the given solutions or do you still need more help here? Do let us know.
Thanks!