topic Re: how to write set analysis expression in back end in QlikView

how to write set analysis expression in back end

rkpatelqlikview — Tue, 17 Apr 2018 13:03:05 GMT

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.

Re: how to write set analysis expression in back end

sunny_talwar — Tue, 17 Apr 2018 13:04:55 GMT

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

Re: how to write set analysis expression in back end

Anil_Babu_Samineni — Tue, 17 Apr 2018 13:24:00 GMT

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

Re: how to write set analysis expression in back end

rkpatelqlikview — Tue, 17 Apr 2018 13:26:53 GMT

thanks for your response Sunny.

How can i use with if condition for same expression?

Re: how to write set analysis expression in back end

rkpatelqlikview — Tue, 17 Apr 2018 13:31:18 GMT

Thanks Anil,

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

Thanks in advance.

Re: how to write set analysis expression in back end

sunny_talwar — Tue, 17 Apr 2018 13:34:49 GMT

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

Re: how to write set analysis expression in back end

Anil_Babu_Samineni — Tue, 17 Apr 2018 13:35:35 GMT

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

Re: how to write set analysis expression in back end

rkpatelqlikview — Tue, 17 Apr 2018 13:39:54 GMT

Sure Anil, Will provide

Re: how to write set analysis expression in back end

vishsaggi — Tue, 17 Apr 2018 14:01:10 GMT

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.

Re: how to write set analysis expression in back end

rkpatelqlikview — Tue, 17 Apr 2018 14:17:20 GMT

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

Re: how to write set analysis expression in back end

rkpatelqlikview — Wed, 18 Apr 2018 04:07:42 GMT

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

Re: how to write set analysis expression in back end

chinnuchinni — Wed, 18 Apr 2018 04:57:59 GMT

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.

Re: how to write set analysis expression in back end

vishsaggi — Wed, 18 Apr 2018 13:31:17 GMT

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