topic Re: Compare Loop Variable to Day(Date) in QlikView

Compare Loop Variable to Day(Date)

BouwerVa — Sat, 16 Nov 2024 01:50:19 GMT

Hi I want to compare a Loop Variable to the Day of a Date Field.

When I hard code the Following Code is work Perfectly:

ExportFile_Future:
NoConcatenate
LOAD Distinct
AEDO_transaction_id,
OPPS,
AM,
BFS_BRANCH_NAME,
EMPL_NAME,
AEDO_employer_code,
AEDO_TX_DATE_YEAR,
//num(tMonth) as tMonth
if(Month(AEDO_tran_date) = Month(AEDO_runout_date),
if(Day(AEDO_tran_date) <= (1) and Day(AEDO_runout_date) >= (1), 'x'),
if(Day(AEDO_tran_date) <= (1) or Day(AEDO_runout_date) >= (1), 'x')) as 1

But when I put it in a loop it returns a Null Value:

For iD = 1 to 4
Trace >>>>> Join Export File '$(iD)' <<<<<;
//Temp:
//NoConcatenate
Left Join (ExportFile_Future)
LOAD
AEDO_transaction_id,
OPPS,
AM,
BFS_BRANCH_NAME,
EMPL_NAME,
AEDO_employer_code,
'Future' as AEDO_TX_DATE_YEAR,
//tMonth,
IsNum($(iD)) as test,
if(Month(AEDO_tran_date) = Month(AEDO_runout_date),
if(Num(Day(AEDO_tran_date)) <= Num($(iD)) and Num(Day(AEDO_runout_date)) >= Num($(iD)), 'x'),
if(Num(Day(AEDO_tran_date)) <= Num($(iD)) or Num(Day(AEDO_runout_date)) >= Num($(iD)), 'x')) as $(iD)
Resident AEDO
Where xml_transaction_type = 'AEDO_FUTURE';

Next iD

Re: Compare Loop Variable to Day(Date)

Taoufiq_Zarra — Wed, 27 Nov 2019 08:59:16 GMT

Hi,

Can you give us more details about the script or complete it?
for example what is the table "AEDO" in Resident, ... or send a sample data.

for example in this code "Left Join (ExportFile_Future)" while (ExportFile_Future) is still empty, it is normal that it returns nulls, each time we join with null.

send us a sample data so that it is easy to interpret the code

Taoufiq

Re: Compare Loop Variable to Day(Date)

BouwerVa — Wed, 27 Nov 2019 09:19:00 GMT

Code with Loop:

Trace Left Join Day Columns;
ExportFile_Future:
NoConcatenate
LOAD Distinct
AEDO_transaction_id,
OPPS,
AM,
BFS_BRANCH_NAME,
EMPL_NAME,
AEDO_employer_code,
AEDO_TX_DATE_YEAR

Resident AEDO
Where xml_transaction_type = 'AEDO_FUTURE';

For iD = 1 to 4
Trace >>>>> Join Export File '$(iD)' <<<<<;
//Temp:
//NoConcatenate
Left Join (ExportFile_Future)
LOAD
AEDO_transaction_id,
OPPS,
AM,
BFS_BRANCH_NAME,
EMPL_NAME,
AEDO_employer_code,
'Future' as AEDO_TX_DATE_YEAR,
//tMonth,
IsNum($(iD)) as test,
if(Month(AEDO_tran_date) = Month(AEDO_runout_date),
if(Num(Day(AEDO_tran_date)) <= Num($(iD)) and Num(Day(AEDO_runout_date)) >= Num($(iD)), 'x'),
if(Num(Day(AEDO_tran_date)) <= Num($(iD)) or Num(Day(AEDO_runout_date)) >= Num($(iD)), 'x')) as $(iD)
Resident AEDO
Where xml_transaction_type = 'AEDO_FUTURE';

Next iD

Drop Tables AEDO, Loans;
Exit Script;

Re: Compare Loop Variable to Day(Date)

BouwerVa — Wed, 27 Nov 2019 09:20:36 GMT

Code without Loop:

(This one works but the I have to repeat code in bold x 31 Times)

Trace Left Join Day Columns;
ExportFile_Future:
NoConcatenate
LOAD Distinct
AEDO_transaction_id,
OPPS,
AM,
BFS_BRANCH_NAME,
EMPL_NAME,
AEDO_employer_code,
AEDO_TX_DATE_YEAR,
//num(tMonth) as tMonth
if(Month(AEDO_tran_date) = Month(AEDO_runout_date),
if(Day(AEDO_tran_date) <= (1) and Day(AEDO_runout_date) >= (1), 'x'),
if(Day(AEDO_tran_date) <= (1) or Day(AEDO_runout_date) >= (1), 'x')) as 1

Resident AEDO
Where xml_transaction_type = 'AEDO_FUTURE';

Drop Tables AEDO, Loans;
Exit Script;

Re: Compare Loop Variable to Day(Date)

marcus_sommer — Wed, 27 Nov 2019 11:44:29 GMT

I suggest that you rethink your whole approach because it creates (with quite heavy join-loops) a crosstable which is usually very expensive to handle (in script and UI). Often there are better ways to create an appropriate datamodel and in your case it might be with the use of an IntervalMatch .

- Marcus