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.

[rkpatelqlikview]

Thanks  Viswanath,

small confusion to do this group for below script. If i do the left join it wont take because that existing table joined with another table. It will give the error. But this Group should be divided based on the T1 table 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];

[chinnuchinni]

you have to do other way then.

here is just try to join the 3rd table with "NRD" - table.

other wise.

try to join the T1 -table and 3rd table then ,store the result table into qvd and the join with "NRD" - table.

[vishsaggi]

Try this? If this does not work you have to share your whole script to look into.

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(Match([Vehicle Number], 'No', 'Yes'), [Vehicle Number]) AS iVMScomm,
[Carrier Code],
[Vehicle Number]
FROM


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

Left Join (transafekm)
LOAD  
[Carrier Code],
If(Count(iVMScomm) <= 5, 'Group 1',
If(Count(iVMScomm) <= 10, 'Group 2',
If(Count(iVMScomm) <= 15, 'Group 3',
If(Count(iVMScomm) <= 20, 'Group 4','Group 5')))) AS Grouping

Resident transafekm

Group By [Carrier Code];