Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
rkpatelqlikview
Creator III
Creator III

how to write set analysis expression in back end

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.

12 Replies
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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rkpatelqlikview
Creator III
Creator III
Author

thanks for your response Sunny.

How can i use with if condition for same expression?

rkpatelqlikview
Creator III
Creator III
Author

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 ..

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rkpatelqlikview
Creator III
Creator III
Author

Sure Anil, Will provide

vishsaggi
Champion III
Champion III

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
Creator III
Creator III
Author

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];