QlikView

Hi, 

I have a field (SMSNo) field where cell numbers are captured in, but i have occasions where people capture  special characters in, i want to created an if statement in my script to identify just the client records  whom has a special character in the (SMSNo) field and where the number does not contain 10 digits. I also want to eliminate the fields that are blank.

Thank you

Hi Team,

I want to create a flag in QlikView Script, If I select entry_date. Total no. of record in entry_date is 644 and I have selected 401 value. So I need a flag like Y for all those value selected and N means not selected records.

Hope you got my answer. Can any one help me on this.

For data related qvw, I am attaching my qvw.

Regards,
Sona_SA

Hi All , 

Please find the attachment of the excel sheet , 

I want to show subtotal and Total  in a table in QlikView . 

I have Department and Category and Sales , 

As shown in the screenshot below is it possible to achive this in Qlikview ? 

Thanks in Advcance 

Hello Folks, 
Looking for help. Can you give some insight to provide Top 3 customers by sales plus top 3 customers by product count.

Appreciate your time and help.

Hello guys,

I have to use the chart scrolling in my graph, but sometimes it opens in the middle, sometimes at the end, or ad the beggining. Is there any logic for that? How can I force it to the end? 

Look at that:

I want it forced to the right, as you can see above.

I mean, it's always at the end, and it desired the user can move it to the left...

I am having trouble in using Filtered Views in the QlikView Dashboard. When i use the Filtered views and run locally dashboard works fine. But, problem occurs when i publish to the QMC & it won't retrieve any results from the view . This problem doesn't occur when i use tables instead of Filtered Views

For eg.,  Using following table works fine

LOAD id,

name

SQL SELECT *

FROM Contact (NOLOCK);

 But if i use the following Filtered View, the view doesn't execute & won't retrieve any results after publishing to QMC.

LOAD id,

name

SQL SELECT  *

FROM FilteredContact (NOLOCK);

Does anyone has a fix to this or any suggestions on how to handle this ?

Hello guys,

I'm using the following expression on a table chart to collect the Growth Rate, and it's working perfectly:

=rate(3,0,-Avg({<Flag = {'3 Year Ago'}>} LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1),Avg({<Flag = {'Current'}>} LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1))

My Table chart has the following dimension:

=if(Date(AsOfDVPG)>today(),'',Date(AsOfDVPG))

I'm trying to show the last available Growth Rate, when the user select a specific field. For that I'm trying to use the following expression:

= FirstSortedValue(
rate(3,0,-Avg({<Flag = {'3 Year Ago'}>} LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1),Avg({<Flag = {'Current'}>} LOPP/IGPD)* FirstSortedValue(TOTAL IGPD, -DVPG, 1))
-DVPG, 1)

But I'm getting an error: "Error: Error in Expression: Nested aggregation not allowed"

How can I do that?

How can I show the last available Growth Rate on a FieldBox?
In this example, I'm expecting to see the 1.25%.

Thanks

Hi everyone! Newbie question here.

I am in the process of transitioning from an Excel based visualization (that eats a TON of memory for everything I am showing) to Qlik. For the Excel version, I have to do a lot of work on the source files before plopping them into the spreadsheet and analyzing them. My hope is to just use the source files with appropriate lookup / joined tables for things I need to do vLookups on.

One specific thing I need to do is evaluate a column during the initial data load and based on the number in the column return a truncated number as a new field. I was able to do this in Excel, but am having a difficult time figuring it out in Qlik. 

Below is a sample of the data (from Excel) and the corresponding output:

Notice the Department ID (AG) has a different prefix based on a new naming convention. The CC (Cost Center - AH) under the "old" naming convention is the last 6 digits of the department ID. Under the "new" naming convention it is the last 5 digits, but it is missing a trailing zero, so I have to add one. I use the Disch Dept CC value to lookup the Disch Dept Division in another table (vLookup in Excel). Here is the formula I used in Excel to get the value in AH:

=IF(AG35<2600000000,NUMBERVALUE(RIGHT(AG35,6)),NUMBERVALUE(CONCATENATE(RIGHT(AG35,5),"0")))

So, what I would like to do is evaluate AG (Disch Dept) during load and return the last 5 or 6 digits (as appropriate) into a new field (Disch Dept CC) and add a zero when necessary. I suspect it will involve a similar If...then statement, but I am unsure of the corresponding "guts" of the operation in Qlik (numbervalue, right, etc...).

Any advice would be welcomed.

Hi guys

Hoping this will be quick and simple, LOL!

I am calculating an average in a chart -  to Average out an Hour_Volume over Weekday(WKDAY) and REQ_HOUR.

REQ_HOUR, for a normal day will contain values from 00 to 23 (count - 24 hours in the day)

Sometimes, though, my REQ_HOUR returns a null value (problem in the underlying data) - so then I have hard-coded REQ_HOUR = 'NULL'.

The problem comes in, in the Average Calc - when there are 24 REQ_HOURS - the Average statement works fine.

However, when there are 25 hours (the 'NULL' included) the Average works incorrectly (that is to be expected)

Now, in the original statement I have at the BOTTOM of this post..how do I EXCLUDE where REQ_HOUR = 'NULL' in the TOTAL statement? so that the calculation is done correctly? 

Much appreciation.

avg({$<PERIOD={$(vMaxMonth)},REQ_DATE=, TYPE_GRAPH = {'HOURLY'}, REQ_HOUR -= {'NULL'}>}total <WKDAY,REQ_HOUR> HOUR_VOLUME)

Hey,

since I work now since 2 months with QlikView and since my company has way to many QlikView-Reports and facing a problem of overall-run time, I want to focus as much as possible on runtime optimization as possible.

But, since also different users are using the same code, I also want to make the code 'pretty'.

So, here my general question, what you guys think:

classic code: 

          Where col <> 'test' or col <> 'dummy' or col <> 'try';

my idea:

          Where IsNull( ApplyMap(col)  )                     

[I would of course put a map beforehand, where I only map 'test', 'dummy' and 'try' with NULL()]

What do you guys think, would it greatly impact the run time? Would you think the code would be easier to read?

Hi! Please let me know is it possible to make to a script to find first day of StatusA and the fist day of StatusB and calculates the days between those.

Data is sort by CustomerID, TransactionData, Status

Hi Team,

I have trying to replace missing value to zero (0) in my Pivot table through expression and script. But not able to replace.

I tried all logic - like - isnull, nullasnull, nullasvalue, alt, len etc. But not success.

Please help me on this. Please look into it, I am attaching the qvw. Please look into it.

Regards,

QV Team

Hello,

I have buttons set up to filter based on date ranges (All Time, Last Month, and Last Week). The All Time and Last Month filters work correctly, but the Last Week filter is having issues. 

Here are both my expressions [where vEndDate = Now()]  

Last Month: =Date(Addmonths(vEndDate,-1),'M/DD/YYYY hh:mm:ss tt')

Last Week: =Date(vEndDate -7,'M/DD/YYYY hh:mm:ss tt')

I have a trigger set on vStartDate and vEndDate to Select in Field for my date field, where the search string is:  ='>='&vStartDate&'<='&vEndDate

If I select Last Month, it sets the search string correctly, but if I select Last Week, it sets - as the date range. 

I am also confused because if I change the value I subtract from vEndDate from 7 to an integer greater than 7, the filter seems to work fine.

Hi All,

My section access script is a below.

Section Access;
LOAD * INLINE [
ACCESS, USERID, ROLE,COUNTRY
ADMIN, DAVE, HEAD,INDIA
USER, MATT, EMP,CHINA
];
Section Application;
myIsland:
LOAD * INLINE [
ROLE,COUNTRY
GH,INDIA
CD,CHINA
];

My req is

1. When DAVE logs in, i need to show a text object saying "Please select a country" since his role is HEAD.

2. And when he clicks on any value in the country list box the list box should disseaper.

Hello guys,

I'm just wondering if this kind of "joints" are a good thing or a bad thing.
Could you help me understanding what's the purpose of these "joints" and if it's a good or a bad thing?

Thank you

Hi all,

I am trying to execute a .vbs file which is running an embedded macro in Excel.
It works from my local pc but when it is deployed to QMC, gets completed successfully but doesn't execute the macro.
All security checks are enabled in settings.ini and .qvw so, don't know what's the problem.

My .qvw script:

EXECUTE cscript.exe "..\..\Folder_1\Folder_2\Script.vbs";

Script.vbs:

Option Explicit

Dim xlApp, xlBook, scriptdir

scriptdir = CreateObject("Scripting.FileSystemObject").GetParentFolderName(WScript.ScriptFullName)
Set xlApp = CreateObject("excel.application")

Set xlBook = xlApp.Workbooks.Open(scriptdir & "\Macro_file.xlsm",0, False)
xlApp.Run "RefreshAll"
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing


Thanks in advance.