Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
poluvidyasagar
Creator II
Creator II

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
sunny_talwar

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);

View solution in original post

7 Replies
Janneke
Creator
Creator

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.

Anil_Babu_Samineni

This works

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
poluvidyasagar
Creator II
Creator II
Author

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

sunny_talwar

May be you are looking for this

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

image.png

sunny_talwar

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
Creator II
Creator II
Author

I keep getting the error during load.

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

Thanks,
Vidya

poluvidyasagar
Creator II
Creator II
Author

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

Thanks Everyone