Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
poluvidyasagar
Contributor

calculate Variable using Let

Hi,

I am trying to calculate a specific date field which consists of weeknumber, yearnumber, daynumber.

I am able to do this using a text box. However, i would like to calculate during the load and assign it to a variable. 

Expression I want to calculate using load is:

=Concat(right(Year(Today()),2) &Week(Today())+4 &Num(WeekDay(Today())))

I am using LET to calculate the value. How do i assign to a variable.

I have attached the qvw file.

Thanks,

Vidya

Labels (1)
1 Solution

Accepted Solutions

Re: calculate Variable using Let

Try this

LET vDate = Right(Year(Today()),2) & Week(Today())+4 & Num(WeekDay(Today()));

Data:
LOAD [Part Number], 
     [Final UP], 
     CNFROM, 
     CNTO
FROM [Data (3).xlsx]
(ooxml, embedded labels, table is Sheet1)
Where not match(CNFROM,'#N/A','0') and CNFROM < $(vDate);
7 Replies
Janneke
Contributor

Re: calculate Variable using Let

Hi Vidya,

Instead of :

Let vDate= Concat(right(Year(Today()),2) &Week(Today())+4 &Num(WeekDay(Today())));

Try:

SET vDate="=Concat(right(Year(Today()),2) &Week(Today())+4 &Num(WeekDay(Today())))";

Works for me in your example for a text object with expression =vDate

Greetings, Janneke.

Re: calculate Variable using Let

This works

Let vDate= '=Concat(right(Year(Today()),2) &Week(Today())+4 &Num(WeekDay(Today())))';

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
poluvidyasagar
Contributor

Re: calculate Variable using Let

Thanks Janneke.

 

I am using this variable as a condition on one of my table. But it gives error. 

Here is the code:

set vDate= Concat(right(Year(Today()),2) &Week(Today())+4 &Num(WeekDay(Today())));

Data:
LOAD [Part Number],
[Final UP],
CNFROM,
CNTO
FROM
Data.xlsx
(ooxml, embedded labels, table is Sheet1)
Where not match(CNFROM,'#N/A','0') and CNFROM < $(vDate);

 I have attached the file. Can you please let me know what is wrong here?

Thanks,
Vidya

Re: calculate Variable using Let

May be you are looking for this

LET vDate = Right(Year(Today()),2) & Week(Today())+4 & Num(WeekDay(Today()))

image.png

Re: calculate Variable using Let

Try this

LET vDate = Right(Year(Today()),2) & Week(Today())+4 & Num(WeekDay(Today()));

Data:
LOAD [Part Number], 
     [Final UP], 
     CNFROM, 
     CNTO
FROM [Data (3).xlsx]
(ooxml, embedded labels, table is Sheet1)
Where not match(CNFROM,'#N/A','0') and CNFROM < $(vDate);
poluvidyasagar
Contributor

Re: calculate Variable using Let

I keep getting the error during load.

Can you please check and let me know where is the mistake.

Thanks,
Vidya

poluvidyasagar
Contributor

Re: calculate Variable using Let

I think i got it. Concat function is LET is causing the issue.

Thanks Everyone