topic How to use if and else command for select which portion of script to load ? in QlikView

How to use if and else command for select which portion of script to load ?

paulyeo11 — Sat, 16 Nov 2019 04:19:39 GMT

Hi All

I have below script work fine.

Some time i need to only run country A. what i do is manually go to country B script comment it.
May i know how to using if then else command to handle it , so that i can just enter 1 or 2 to select which country to run.
Hope some one can advise me.

Paul Yeo

// sales COUNTRY A
sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\Q_DOD_2018.txt (ansi, fix, no labels, header is 0, record is line);

// sales COUNTRY B
Concatenate
LOAD
'PMC' as SOURCE,
Date( Date#([AR Invoice Date], 'DD.MM.YY'), 'DD/YY/YYY') as [date],
[Customer Name] AS COMPANY,
[Row Total (SGD)] as sales
FROM [C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\RAW DATA SAP\SO_PMC_2019.xlsx]
(ooxml, embedded labels, table is SO_PMC_2019);

// AR COUNTRY A
Payment:
LOAD
'TDS' as SOURCE,
@1:9T as [cust_id_ar],
left(@10:38T,25) as [company_ar],
if(right(@73:90T,1)='-', '-' & left(@73:90T, len(@73:90T)-1),@73:90T) as total_ar,
Today() - @98:110T as [total number of day due]

FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\AR_Aging.TXT (ansi, fix, no labels, header is 0, record is line);

// AR COUNTRY B
Concatenate
LOAD
'PMC' as SOURCE,
[Customer/Vendor Code] as [cust_id_ar],
[Customer/Vendor Name] as company_ar,
Today() - Date( Date#([AR Invoice Date], 'DD.MM.YY'), 'DD/YY/YYY') as [total number of day due],
[Outstanding w/o Tax (LC)] as total_ar
FROM [C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\RAW DATA SAP\AR_PMC.xlsx]
(ooxml, embedded labels, table is AR_PMC);

tmp:
LOAD
min(date) AS MinDate,
max(date) AS MaxDate
RESIDENT sales;
MaxMinDate:
NOCONCATENATE LOAD
MIN(MinDate) AS MinDate,
MAX(MaxDate) AS MaxDate
RESIDENT tmp;
DROP TABLE tmp;
LET varMinDate = Num(Peek('MinDate'));
LET varMaxDate = Num(Peek('MaxDate'));
LET vToday = num(today());
DROP TABLE MaxMinDate;
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate,
date($(varMinDate) + rowno() - 1) AS D,
year($(varMinDate) + rowno() - 1) AS Y,
month($(varMinDate) + rowno() - 1) AS M,
date(monthstart($(varMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

MasterCalendar:
LOAD TempDate AS date,
day([TempDate]) as [day],
num(month([TempDate])) as [month],
Y AS year
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;

Re: How to use if and else command for select which portion of script to load ?

agni_gold — Sat, 16 Nov 2019 17:10:48 GMT

Check if you can do the Partial reload option here, that might help you.
Another point, how you are deciding, when to load A or B?

Re: How to use if and else command for select which portion of script to load ?

paulyeo11 — Sat, 16 Nov 2019 23:01:43 GMT

Hi Gold

Thank you for your reply.

You mentioned partial reload. Which I am not sure what you mean.

You also mentioned in what situation I choose to run A or B ? Good question . Actually I just want to skip some of the load script to speed up the testing.

Paul

Re: How to use if and else command for select which portion of script to load ?

agni_gold — Sun, 17 Nov 2019 07:34:18 GMT

In Qlikview there is an option for Partial reload,
but for qliksense , please see below post

https://support.qlik.com/articles/000046093

Re: How to use if and else command for select which portion of script to load ?

paulyeo11 — Sun, 17 Nov 2019 07:38:43 GMT

Hi Sir

I think you must have Mia-undrestand my question . My issue is not about partial reload.

Paul

Re: How to use if and else command for select which portion of script to load ?

agni_gold — Sun, 17 Nov 2019 07:42:51 GMT

i understood your question, but you need to get on what conditions you want to run a particular script, otherwise, it is not directly possible as per my knowledge.

you need to tweak your solution.

Re: How to use if and else command for select which portion of script to load ?

paulyeo11 — Sun, 17 Nov 2019 07:47:43 GMT

Hi Sir

I am sorry that I though you not understand.

May I know how you make use for partial reload to solve my issue ?

Paul

Re: How to use if and else command for select which portion of script to load ?

Brett_Bleess — Wed, 20 Nov 2019 14:47:01 GMT

Paul, have a look at the following Help links, hopefully this may make some sense, along the Partial Reload suggestion, I do believe this should work as noted by the other poster.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Partial%20Reload.htm

I think you likely would want to use the Replace option on the load of Country A when running the partial reload, but I do not know if you need to load all the other tables or not etc.

Best bet may be to create a new app and play around with things a bit there until you get comfortable with what is happening when... Hopefully this helps you move things forward.

Regards,
Brett