How do I (in the data load editor) check to see if a field is null or empty and then if it is not empty proceed to subtract another field from the field I was checking? I want to calculate the number of days between open and close dates ([open_date] and [close_date]) for all the records where close_date was populated with a date.

The line I commented out is giving me an error

Hi,

Someone helped me write this script, so it's a bit beyond my capabilities.  It is converting all income data whatever currency or distance measurement is used into USD and miles.  For some reason it is loading my data multiple times and I don't know why. 

conversionMap:
Mapping LOAD
AutoNumberHash128(Curnc_Year,Currency_Abbr,'currency') as ConversionKey,
USD_Equivalent
FROM [lib://Documents (1qconsulting_debi.cook)/Currency Conversion.xlsx]
(ooxml, embedded labels, table is Sheet1);

TempTable:
LOAD
YearID as TestYearID,
SubtextRow as Testsubtext,
[Year] as testyear,
if (SubtextRow = 'United Arab Emirates Dirham', 'AED', if(SubtextRow = 'Canadian Dollars', 'CAD')) as CurrencyAbbr
Resident [ResponseTable]
where match(SubtextRow, 'Canadian Dollars', 'United Arab Emirates Dirham')>0;

left Join ([ResponseTable])
Load
TestYearID as YearID,
testyear as Year,
CurrencyAbbr
resident TempTable;
drop table TempTable;

TempTable:
LOAD
YearID as TestYearID,
SubtextRow as Testsubtext,
[Year] as testyear,
if (SubtextRow = 'Kilometers','Kilometer') as DistKilometer
Resident [ResponseTable]
where match(SubtextRow,'Kilometers')>0;

left Join ([ResponseTable])
Load
TestYearID as YearID,
testyear as Year,
DistKilometer
resident TempTable;
drop table TempTable;

Data:
NoConcatenate Load
*,
if(Conversion = 'currency', Response / ApplyMap('conversionMap',AutoNumberHash128(num#(Year),CurrencyAbbr,Conversion),1),
if(Conversion = 'distance' and DistKilometer = 'Kilometer', Response/1.60934,
if(Conversion = 'sqmile' and DistKilometer = 'Kilometer', Response/1.60934*1.60934 ,
if(Conversion = 'acre' and DistKilometer = 'Kilometer', Response/.404686, Response) )))
as ConvertedResponse,
ApplyMap('conversionMap',AutoNumberHash128(num#(Year),CurrencyAbbr,Conversion),1) as USD_Equivalent
resident [ResponseTable];
drop table [ResponseTable];

drop field Response from Data;
rename field ConvertedResponse to Response;

Thanks

Hello, 

When I use column Item Value_EGNetUSD  with SUM function ( sum(distinct[Item Value_EGNetUSD]) I do get a sum total value = 9.6 K which differs when I use the same column as KPI (Key performance indicator)  = 7.26k. Please can anyone let me know why is this difference. Attached example with 10 lines of data for reference. Your assist is highly appreciated 

KPI value - 7.26K

Manually downloaded and check - 9.5K

 KPI as 

Hi team,

I am trying to find Newsstand reports on NPrinting server drive. Can I know where NPrinting stores newsstand reports?

Secondly, Newsstand keeps reports up to 180 days, how to increase this time limit?

Thanks,

Rohit

Hello, 

Hoping someone can help me determine whether it is possible to implement the following logic in Qlik, or whether I need to change my dataset first before i load to Qlik.  The datasource is a csv file.

In the Straight Table I have the following (see screenshot):

• Dimensions: date, Strategy, Contract (only one visible in screenshot but there are many), Symbol, Buy/Sell (please ignore this dimension), Commission
• Expressions: Quantity, Gross Notional, P&L (commission * Gross Notional)

I need to somehow implement the following logic:

• IF MATCH "date" AND MATCH "Strategy" AND MATCH "Contract" but "Symbol" does NOT MATCH
• Then "Commission" is divided by 2.

So for Person C and Person D, the commission figure would move from 0.04% to 0.02% on both lines.

For Person A there is added complication. If Quantity is not the same on both lines, then the commission rate should only be 0.025% for the lower quantity (1258 in this case). The difference of 74 (1258-1332) should be at 0.05%. 

Not sure if this is achievable in Qlik but would really appreciate any guidance on how this could be achieved! Thanks!!

Hello everyone,

Let's say i do have 2 hierarchies, city and country

I am trying to create a measure which for each city will give me the avg sales for the whole country

I tried this : 

= avg({$1<Country=p()>} Sales) and it worked

But whenever i do filter with a dimension that is not in the hierarchy, per exemple type of sales, it will bring also the sales of other countries since i am asking in my formula all the possible values of each filter ...

Then i tried this avg({$1<Country, Type=p()>} Sales) and it worked again, but when i filter with the country, im getting again a wrong result with all the possible combinations of country and type ...

Do you have a trick to overcome this problem ? 

Thanks

Hello dear community I would like to know if someone could help me, I need to integrate a Recaptcha v2 in login.htm of the access point, has someone made this integration? Thank you

Hi Qlik Community,

I have used this YouTube video to upload user via Excel into the QlikSense QMC.

In the video, there is a reference to "customtype" values, but no practical example of how exactly to enter these into the Excel to upload a user with Custom Properties attached.

In the "Attributes table name" Excel sheet, if I have a Custom Property name STREAM_ACCESS and want to attribute a user a value of ANALYTICS (which is a value already created in STREAM_ACCESS) what are the exact values I need to put in the Excel sheet's "type" and "value" columns to update the user's Custom Properties.

Many thanks for practical advice and experience you have on this.

Kind regards,

Cheenu

Hello Qlik Community Admin team,

I used to be able to post to "documents" thread under Sense and View, but I get a "permissions" error message whenever I try to create a new document now.

I contacted Mike Tarallo to ask for his advice and he has pointed me towards you. Could you please help me out with this?

Many thanks for your help in this matter.

Kind regards,

Cheenu

I have to query data from SQL based system. Due to the limitations and the available bandwidth, I cannot load the entire dataset in one SQL go.

How do I write my SQL query to pull 1 day worth of data and then loop through it until I have all the data till the maximum date?

I have a date field -RecordDate in the data table that I can use to identify the record date.