Set analyse in bill of material

[jozisvk11]

Hello,

I have bill of material and I need to filter only item, which start with 'J'.

Bill of material:

Niveau 1	Niveau 2	Niveau 3	Niveau 4	Niveau 5	Niveau 6
T1017	T1018	T1019	J0001
T1020	T1021	T1023	T1028	J0002
T1030	T1031	T1032	T1034	T1035	J0001
T1038	T1045	T1039	J0002
T1040	T1037	J0003
T1050	T1052	J0007
T1055	T1053	J0005
T1057	T1054	T1058	T1059	J0007
T1062	T1064	T1065	T1068	T1069	J0002

....

My result table should be:

J**** item	Niveau 1	Niveau 2
J0001	T1017	T1018
J0001	T1030	T1031
J0002	T1020	T1021
J0002	T1038	T1045
J0002	T1062	T1064
J0003	T1040	T1037
J0005	T1055	T1053
J0007	T1050	T1052
J0007	T1057	T1054

Could you help me with my problem.

Thank you

[Anil_Babu_Samineni]

Not sure, Where i need to start your query. But this makes bit easy way for you

If(WildMatch(Item, 'J*'), Item) // This condition returns only starts from J..

[jozisvk11]

But J**** item can be in different Niveau.

[effinty2112]

Hi Josef,

Try

Data:

CrossTable(Temp, JItem,2)

LOAD * INLINE [

    Niveau 1, Niveau 2, Niveau 3, Niveau 4, Niveau 5, Niveau 6

    T1017, T1018, T1019, J0001

    T1020, T1021, T1023, T1028, J0002

    T1030, T1031, T1032, T1034, T1035, J0001

    T1038, T1045, T1039, J0002

    T1040, T1037, J0003

    T1050, T1052, J0007

    T1055, T1053, J0005

    T1057, T1054, T1058, T1059, J0007

    T1062, T1064, T1065, T1068, T1069, J0002

];

Result:

LOAD

JItem,

[Niveau 1],

[Niveau 2]

Resident Data

Where WildMatch(JItem,'J*');

Drop table Data;

To get:

JItem	Niveau 1	Niveau 2
J0001	T1017	T1018
J0001	T1030	T1031
J0002	T1020	T1021
J0002	T1038	T1045
J0002	T1062	T1064
J0003	T1040	T1037
J0005	T1055	T1053
J0007	T1050	T1052
J0007	T1057	T1054

Regards

Andrew

[jozisvk11]

I can´t do it in script because I have this table in data model and this table has 1 300 000 rows. I write only few rows for explanation my problem. ...

[Kushal_Chawda · Accepted solution]

create straight table

Dimension:

Niveau 1

Niveau 2

Expression:

=Only({<[Niveau 3]={'J*'}>}[Niveau 3])&Only({<[Niveau 4]={'J*'}>}[Niveau 4])&Only({<[Niveau 5]={'J*'}>}[Niveau 5])&Only({<[Niveau 6]={'J*'}>}[Niveau 6])

[Anil_Babu_Samineni]

I remember one time sunny did this in Front end. stalwar1‌ look this.

[sunny_talwar]

What kush141087‌ did seems like an option.... unless Niveau column is not definite

[jozisvk11]

It works, Thank you !!!

[Kushal_Chawda]

thanks sunny bhai