Filter on variable columns

1

Hi Dmitry

I have to extract a data set based on multiple variables.

In this example, I need to create 7 different datasets.

Apply filters to Column1 and Column2 in pack one and applies filters to Column1 Column2 and Column3 for pack 2 etc. Data will then be extracted from Table1. Any suggestions?

Criteria:
Filter

Many thanks

2

You can use the Iterate action and pass 4 parameters into the iterated project. Each parameter would correspond to Column1, Column2, etc. E.g.:

Param1 = Column1
Param2 = Column2
Param3 = Column3
Param4 = Column4

Then, in the iterated project you can load Table1 from a dataset file, and apply an expression filter as follows:

if(not isempty({Param1}), [Column1]={Param1}, true()) 
and if(not isempty({Param2}), [Column2]={Param2}, true()) 
and if(not isempty({Param3}), [Column3]={Param3}, true()) 
and if(not isempty({Param4}), [Column4]={Param4}, true())

The true() part is needed because the filtering is done using the logical AND operator between several conditions.

Alternatively, you can pass Table1 into the iterated project with the help of the “Iterate table” action instead of using a dataset file, but that’s more advanced technique.

Let me know if you need an example.

3

Thanks. I am going to try this and let you know

4

I cannot get this to work. I will appreciate any advise.

Attached please find dummy files with only a number of columns.

The following:

  1. Want to create a leader pack (Pack Request) - it will also show inclusions (Grades) and Exclusion
  2. Take leader ID and find it in All Data
  3. Then take the org structure from the leader and filter Target.
  4. Exclude Leader from this group.
  5. Exclude any ID as in Leaders pack
  6. Exclude bands as in leaders pack
  7. Group all together and create export for i.e. pack 2

Hope this makes sense

Thanks

LeadersRequesttest.morph (3.4 KB)
PackRequest.xlsx (9.8 KB)
TblTarget.xlsx (25.9 KB)
TblAll.xlsx (23.2 KB)
MainFile.morph (5.5 KB)

5

Hi Rykie,

sorry, I won’t be able to make the entire solution for you. However, you can use the example below to filter by multiple columns and produce various subsets from the same dataset (which was your original question):

This example takes 3 sets of parameters and produces 3 datasets (packs) appended into one table. Datasets can be distinguished by [Dataset ID]. The main project is filter-params.morph which iterates filter.morph. For simplicity, the 3 sets of filters are produced from the main dataset using a sequence of actions.

filter.morph (1.9 KB)
filter-params.morph (5.7 KB)
Companies.dset (150.4 KB)

image
image.png1331×610 36.7 KB

6

Thank you.