how to write set analysis expression in back end

[rkpatelqlikview]

Hi Experts,

Please help on this. How can i write this set analysis expression in backend?

I used this in text object but now need to put a filter.

If(Count({<iVMScomm={'Yes'}+{'No'}>}  [Vehicle Number]) <= 5, 'Group 1',

  If(Count({<iVMScomm={'Yes'}+{'No'}>}  [Vehicle Number]) <= 10, 'Group 2',

  If(Count({<iVMScomm={'Yes'}+{'No'}>}   [Vehicle Number]) <= 15, 'Group 3',

                If(Count({<iVMScomm={'Yes'}+{'No'}>}  [Vehicle Number]) <= 20, 'Group 4','Group 5'))))

Thanks in advance.

[sunny_talwar]

There is no set analysis in the back end... but you can use if statements....

[Anil_Babu_Samineni]

You can use List box as expression and paste the same for filters?

[rkpatelqlikview]

thanks for your response Sunny.

How can i use with if condition for same expression?

[rkpatelqlikview]

Thanks Anil,

After build this logic in the backend, i need to build a logic for Rank with in the Group.

Thanks in advance.

[sunny_talwar]

you will need to provide more information in order to get a detailed answer.

[Anil_Babu_Samineni]

Can you share the script which includes iVMScomm, [Vehicle Number] ?? Because, When you used Aggregate in If..else.. condition you need Group By in script that part we need to better clear ..

[rkpatelqlikview]

Sure Anil, Will provide

[vishsaggi]

It would be good if you share some sample. However, for now I made a sample for you just incorporate accordingly in your actual data.

Try this?

CountEg:
LOAD *, IF(Match(iVMScomm, 'Yes','No'), VehicleNo) AS VhiNo;
LOAD iVMScomm,
VehicleNo
FROM
CountEg.xlsx
(ooxml, embedded labels, table is Sheet1);

LEFT JOIN(CountEg)
LOAD VehicleNo,
If(Count(VhiNo) <= 5, 'Group 1',
  If(Count(VhiNo) <= 10, 'Group 2',
  If(Count(VhiNo) <= 15, 'Group 3',
  If(Count(VhiNo) <= 20, 'Group 4','Group 5')))) AS Grouping

Resident CountEg
Group By VehicleNo;

Attached sample excel file for this script.

[rkpatelqlikview]

Please find the script.

Group should be with T1 Data.

NRD:

LOAD

     [Vehicle Number],

   [Device Number]

FROM

(ooxml, embedded labels, table is [Non Reporting Details]);

join(transafekm)

T1:

LOAD

//ApplyMap('NRD', [Vehicle Number], 'No') as  iVMScomm,

//[Carrier Code] &'|'&  [Vehicle Number]   as Key,

If(Exists([Vehicle Number]), 'No', 'Yes') AS iVMScomm,

[Carrier Code],

   [Vehicle Number]

FROM

(ooxml, embedded labels, table is [CURRENT FLEET]);

Left Join T1

LOAD    [Carrier Code],

If(Count((iVMScomm='Yes'+'No') ) <= 5, 'Group 1',

If(Count(iVMScomm='Yes'+'No')<= 10, 'Group 2',

If(Count(DISTINCT [Vehicle Number]) <= 15, 'Group 3', If(Count(DISTINCT [Vehicle Number]) <= 20, 'Group 4','Group 5')))) as Group

Resident T1

Group By [Carrier Code];