topic Need to count values from two separate fields in QlikView

Need to count values from two separate fields

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have two fields called Fax Type In and Fax Type Out. Here are my IF statements which load the appropriate data.

Load *,

If([Originator_Group] = 'TCMFPQ4', 'MFP',

If([Originator_Group] LIKE 'MXBPOS*', 'Efax',

If([Originator_Group] LIKE 'SC*', 'SAP',

If([Originator_Group] = 'ExpRpt' OR

[Originator_Group] LIKE 'TCL*' OR

[Originator_Group] LIKE 'NPO*' OR

[Originator_Group] LIKE 'SES*' OR

[Originator_Group] LIKE 'PC*' OR

[Originator_Group] LIKE 'TCS*', 'FI',

If(len(Originator_Group) < 1, 'External', 'Other'

)))))as [Fax Type In]

;

Load *,

If([Recipient_Group] = 'MFPTEMPL', 'MFP',

If([Recipient_Group] LIKE 'MXBPOS*', 'Efax',

If([Recipient_Group] LIKE 'SC*', 'SAP',

If([Recipient_Group] = 'ExpRpt' OR

[Recipient_Group] LIKE 'TCL*' OR

[Recipient_Group] LIKE 'NPO*' OR

[Recipient_Group] LIKE 'SES*' OR

[Recipient_Group] LIKE 'PC*' OR

[Recipient_Group] LIKE 'TCS*', 'FI',

If(len(Recipient_Group) < 1, 'External','Other'

)))))as [Fax Type Out]

Now, I want to be able to construct a Pie Chart which totals the values from Originator Group and Recipient Group for

MFP

Efax

External

Other

Re: Need to count values from two separate fields

breno_morais — Wed, 22 Aug 2018 16:52:09 GMT

I do not understand, can you explain better? You need the Originator Group and Recipient Group values in One Pie chart?

Re: Need to count values from two separate fields

Anonymous — Wed, 22 Aug 2018 17:00:13 GMT

I want a Pie Chart which calculates the totals for each Recipient Group and Originator group value..

So i want

Fax Type IN MFP + Fax Type Out MFP

Fax Type In Efax + Fax Type Out Efax

etc.. etc..

I can make pie charts for each fax type.. but i want One Pie chart which calculates both..

Re: Need to count values from two separate fields

vishsaggi — Wed, 22 Aug 2018 17:04:55 GMT

Are you using Count() aggr function here? Can you share a sample file to look into?

How are the two tables joined? You can do a left join to second table on Fax Type Out field like

LEFT JOIN(Table1)

LOAD Key,

[Fax Type Out] AS [Fax Type IN]

Resident Table2Name;

Re: Need to count values from two separate fields

Anonymous — Wed, 22 Aug 2018 18:06:06 GMT

=count([Fax Type Out]) is the simple expression i use to get the totals for (MFP, Efax, FI,etc..)

Everything is in one table..

Re: Need to count values from two separate fields

vishsaggi — Wed, 22 Aug 2018 18:27:30 GMT

Do a left join like this and see.

Load *,

If([Originator_Group] = 'TCMFPQ4', 'MFP',

If([Originator_Group] LIKE 'MXBPOS*', 'Efax',

If([Originator_Group] LIKE 'SC*', 'SAP',

If([Originator_Group] = 'ExpRpt' OR

[Originator_Group] LIKE 'TCL*' OR

[Originator_Group] LIKE 'NPO*' OR

[Originator_Group] LIKE 'SES*' OR

[Originator_Group] LIKE 'PC*' OR

[Originator_Group] LIKE 'TCS*', 'FI',

If(len(Originator_Group) < 1, 'External', 'Other'

)))))as [Fax Type In]

;

LEFT JOIN (Tablenamefromtop)

Load *,

If([Recipient_Group] = 'MFPTEMPL', 'MFP',

If([Recipient_Group] LIKE 'MXBPOS*', 'Efax',

If([Recipient_Group] LIKE 'SC*', 'SAP',

If([Recipient_Group] = 'ExpRpt' OR

[Recipient_Group] LIKE 'TCL*' OR

[Recipient_Group] LIKE 'NPO*' OR

[Recipient_Group] LIKE 'SES*' OR

[Recipient_Group] LIKE 'PC*' OR

[Recipient_Group] LIKE 'TCS*', 'FI',

If(len(Recipient_Group) < 1, 'External','Other'

))))) as [Fax Type In]

Re: Need to count values from two separate fields

Anonymous — Wed, 22 Aug 2018 18:38:08 GMT

This is a SQL Query.. I used the Table Name that populated for my data but it did not recognize the Table Name..

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;

Re: Need to count values from two separate fields

vishsaggi — Wed, 22 Aug 2018 18:44:56 GMT

can you share your actual script you were running to have a quick look?

Re: Need to count values from two separate fields

Anonymous — Wed, 22 Aug 2018 19:07:28 GMT

//Report Certification

if WildMatch(ComputerName(),'TAG*') then

Trace The dashboard is being reloaded via the desktop;

vCommonPath = 'xxxxxx'

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=TCReport;Data Source=wfaxrpt01p;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=TAGxxxx;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is xxxxx);

ELSE

Trace The dashboard is being reloaded on the server;

vCommonPath = 'E:\QlikStorage\CommonFiles'

ENDIF;

// Below we are including the files to be used for Application Certification

$(Must_Include=$(vCommonPath)\FE Standard Include.txt);

$(Must_Include=$(vCommonPath)\FESCertifiedApplicationList\Include Files\IsCertified.qvs);

$(Include=$(vDataConnectionPath)\TCReport.txt);

/*-----------------------------------Required Script------------------------------------------------------*/

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

//criteria for Efax: begins with “MXBPOS”

//criteria for SAP fax: begins with “SC”

//criteria for FI fax: Equals "ExpRpt", begins with “TCL”, begins with “NPO”, begins with “SES”, begins with “PC”, begins with “TCS”

Load *,

If([Originator_Group] = 'TCMFPQ4', 'MFP',

If([Originator_Group] LIKE 'MXBPOS*', 'Efax',

If([Originator_Group] LIKE 'SC*', 'SAP',

If([Originator_Group] = 'ExpRpt' OR

[Originator_Group] LIKE 'TCL*' OR

[Originator_Group] LIKE 'NPO*' OR

[Originator_Group] LIKE 'SES*' OR

[Originator_Group] LIKE 'PC*' OR

[Originator_Group] LIKE 'TCS*', 'FI',

If(len(Originator_Group) < 1, 'External', 'Other'

)))))as [Fax Type Inbound]

;

Load *,

If([Recipient_Group] = 'MFPTEMPL', 'MFP',

If([Recipient_Group] LIKE 'MXBPOS*', 'Efax',

If([Recipient_Group] LIKE 'SC*', 'SAP',

If([Recipient_Group] = 'ExpRpt' OR

[Recipient_Group] LIKE 'TCL*' OR

[Recipient_Group] LIKE 'NPO*' OR

[Recipient_Group] LIKE 'SES*' OR

[Recipient_Group] LIKE 'PC*' OR

[Recipient_Group] LIKE 'TCS*', 'FI',

If(len(Recipient_Group) < 1, 'External','Other'

)))))as [Fax Type Outbound ]

;

Load*, if(SenderFaxLineStatus = 'External','External Sender','Internal Sender') as [Sender Status],

if(ReceiverFaxLineStatus = 'External', 'External Recipient', 'Internal Recipient') as [Receiver Status];

Load*, if(SenderFaxLineStatus = 'External','External Sender',

if(ReceiverFaxLineStatus = 'External', 'External Recipient')) as [External Faxes],

if(SenderFaxLineStatus = 'Internal','Internal Sender',

if(ReceiverFaxLineStatus = 'Internal', 'Internal Recipient')) as [Internal Faxes];

Load*, if(len(trim(MsgClass_Originator))=0, 'Internal', 'External') as SenderFaxLineStatus

, if(len(trim(MsgClass_Recipient))=0, 'Internal', 'External') as ReceiverFaxLineStatus

;

LOAD *, num(month(TimeSent)) & '/' & day(TimeSent) & '/' & year(TimeSent) as Date, /* Formatting TimeSent to Month, Day, and Year*/

Time(TimeSent,'h:mm:ss TT') as Time,

Time(TimeSent,'h TT') as HourTT,

TimeStamp(TimeStamp#(Left(TimeStamp(TimeSent, 'MM/DD/YYYY hh:mm:ss'), 13), 'MM/DD/YYYY hh'), 'MM/DD/YYYY hh TT') as [Date|Hour],

time(MakeTime(hour(Time(frac(TimeSent))),0,0),'h TT') as GroupedHour

;

Load *,

month(TimeSent) as Month, /* creating Month Field */

day(TimeSent) as Day, /* creating Day Field */

year(TimeSent) as Year, /* creating Year Field */

Hour(TimeSent) as Hour

;

SQL SELECT TOP 100000 /*Begin SQL*/

"ID_Action" /* Unique fax ID*/

,"Time_Created" as "TimeArrive" /*Time fax hit kofax*/

,"Time_Action" as "TimeSent" /*Time kofax sent out fax */

,"Message_Class" /* IF message class is blank (SAP, FI, or MFPGUEST?) recipient */

,"Duration" /*duration of sending fax in seconds*/

,"Originator" /* IF originator is F, then this is an external fax sender, that came in on a fax line */

,"Originator_Info" /*details of sender (name, phone number, FI/SAP link, etc.)*/

,"Originator_Group" /*Groups associated with User Profiles in Kofax */

,"Recipient" /*IF recipient is F, then fax was sent to an externally dialed number on a fax line */

,"Recipient_Info" /*details of fax recipient (name, phone number, FI/SAP link, etc.)*/

,"Recipient_Group" /*Groups associated with User Profiles in Kofax */

,"Size" /*File size of fax*/

,"Pages" /*Number of pages of fax*/

,"TXT_STATE" /*Status of fax (Success or Fail)*/

,"Delay" /*Delay time of fax (seconds)*/

,"MsgClass_Originator" /*IF value is "FAX", appears that sender used an external line*/

,"MsgClass_Recipient" /*IF value is "FAX", appears that a fax line was used to receive fax into Kofax */

,"TS_CHANNEL" /*Fax line number used for fax, I believe "ZZ" indicates no fax line was used and kept internal*/

,"TS_REC_CHANNEL" /*Fax line number used to receive incoming fax to Kofax*/

,"Channel_Group_X" /* If value is "F" a fax line was used for either receiving or sending a fax */

,"Channel_X" /* The fax line channel used by Kofax for either receiving or sending a fax */

,"ChG_in" /*If value is "F" then a fax line was used to receive incoming fax to Kofax*/

,"ChG_out" /*If value is "F" then a fax line was used to send fax out from Kofax */

,"Subject" /*Subject of Fax*/

FROM TCReport.dbo."Action_Table_Descr" /*Original Table */

WHERE "Recipient_Group" != 'IPPRINT' /*Filtering out fax delivery/non delivery notifications*/

ORDER BY "TimeArrive" DESC /*Filter by most recent records*/

; /*End SQL*/

/*-----------------------------------End Required Script------------------------------------------------------*/

Re: Need to count values from two separate fields

vishsaggi — Wed, 22 Aug 2018 20:43:37 GMT

Can you try this?

Load *,

If([Originator_Group] = 'TCMFPQ4' OR [Recipient_Group] = 'MFPTEMPL', 'MFP',

If([Originator_Group] LIKE 'MXBPOS*' OR [Recipient_Group] LIKE 'MXBPOS*', 'Efax',

If([Originator_Group] LIKE 'SC*' OR [Recipient_Group] LIKE 'SC*', 'SAP',

If([Originator_Group] = 'ExpRpt' OR [Recipient_Group] = 'ExpRpt' OR

[Originator_Group] LIKE 'TCL*' OR [Recipient_Group] LIKE 'TCL*' OR

[Originator_Group] LIKE 'NPO*' OR [Recipient_Group] LIKE 'NPO*' OR

[Originator_Group] LIKE 'SES*' OR [Recipient_Group] LIKE 'SES*' OR

[Originator_Group] LIKE 'PC*' OR [Recipient_Group] LIKE 'PC*' OR

[Originator_Group] LIKE 'TCS*' OR [Recipient_Group] LIKE 'TCS*', 'FI',

If(len(Originator_Group) < 1 OR len(Recipient_Group) < 1, 'External', 'Other'

)))))as [Fax Type In-Out_bound]

;

Then your rest of your script.

---------------------------------------------------------------------------------------

Note:

Remove below from your original script.

Load *,

If([Recipient_Group] = 'MFPTEMPL', 'MFP',

If([Recipient_Group] LIKE 'MXBPOS*', 'Efax',

If([Recipient_Group] LIKE 'SC*', 'SAP',

If([Recipient_Group] = 'ExpRpt' OR

[Recipient_Group] LIKE 'TCL*' OR

[Recipient_Group] LIKE 'NPO*' OR

[Recipient_Group] LIKE 'SES*' OR

[Recipient_Group] LIKE 'PC*' OR

[Recipient_Group] LIKE 'TCS*', 'FI',

If(len(Recipient_Group) < 1, 'External','Other'

)))))as [Fax Type Outbound ]

;

Re: Need to count values from two separate fields

Anonymous — Thu, 23 Aug 2018 14:16:27 GMT

That worked! Thank you! But, Can i keep the Recipient_Group Load statement, so i can display the Fax Type Outbound?

Re: Need to count values from two separate fields

Anonymous — Thu, 23 Aug 2018 14:29:53 GMT

On second observation.. that did not work.. It only returned one Fax type.. I need two fax types per table entry.. An inbound fax type and an outbound fax type.. Then i need to add up all the fax types..

Re: Need to count values from two separate fields

Anonymous — Thu, 23 Aug 2018 14:41:24 GMT

TO put simply.. I need to add all the common values from these two columns in my table..

Re: Need to count values from two separate fields

vishsaggi — Thu, 23 Aug 2018 15:14:20 GMT

Can you share qvw file with this data or a qvd file with this data to work on. You can mask thedata if you have any confidential information following these steps. This way it will save time at my end to try it out.

Preparing examples for Upload - Reduction and Data Scrambling

Re: Need to count values from two separate fields

Anonymous — Thu, 23 Aug 2018 15:43:02 GMT

There ya go.. Thanks!

Re: Need to count values from two separate fields

vishsaggi — Fri, 24 Aug 2018 00:58:31 GMT

Sorry got stuck with some work. Will look into it tomorrow.

Re: Need to count values from two separate fields

Anonymous — Mon, 27 Aug 2018 19:57:26 GMT

Any further thoughts?

Re: Need to count values from two separate fields

vishsaggi — Tue, 28 Aug 2018 22:31:23 GMT

Sorry for the delay. Try this. Add the remaining fields in your first Load statement and your daterange and date script.

Korfax:

LOAD

Date,

Date|Hour,

Day,

[Fax Type Inbound],

[Fax Type Inbound] AS Inbound,

[Fax Type Outbound] AS Outbound,

GroupedHour,

Hour,

HourTT,

Originator,

Originator_Group,

Originator_Info,

[Receiver Status],

Year

FROM

[..\KofaxReporting.xlsx]

(ooxml, embedded labels, table is Document_TB02);

LOAD

[Fax Type Outbound] AS [Fax Type Inbound],

Count([Fax Type Outbound]) AS OutboundCnt

FROM

[..\KofaxReporting.xlsx]

(ooxml, embedded labels, table is Document_TB02)

Group By [Fax Type Outbound];