topic Re: Load script data where Date XY in App Development

Load script data where Date XY

KristianK — Thu, 29 Aug 2019 11:21:41 GMT

Hi,

I am new to Qlik Sense and have a small question regarding my script.

LOAD text("Item No_"),
text("Sales Type"),
text("Sales Code"),
Date("Starting Date",'DD.MM.YYYY'),
"Unit Price",
if("Ending Date" <=Today(),Date("Ending Date",'DD.MM.YYYY')) ;

SQL SELECT "Item No_",
"Sales Type",
"Sales Code",
"Starting Date",
"Unit Price",
"Ending Date"

I want to load this data from via an ODBC connection. I want only data where the "Ending Date" is "<= today", otherwise I have million entries... can somebody tell me what I did wrong in the bold section?

Thank you in advance for your help!

Re: Load script data where Date XY

KristianK — Thu, 29 Aug 2019 13:14:57 GMT

I changed to "Where" function but it' is not working. I want to show data where the ending Date is ='01.01.1753' or >= Today

But i get this error message that "Today" is not understood. Any help would be appreciated. Thank you

Re: Load script data where Date XY

Anil_Babu_Samineni — Thu, 29 Aug 2019 13:18:38 GMT

Perhaps this?

LOAD text("Item No_"),
text("Sales Type"),
text("Sales Code"),
Date("Starting Date",'DD.MM.YYYY'),
"Unit Price",
"Ending Date" Where Date("Ending Date",'DD.MM.YYYY") <= Date(Today(), 'DD.MM.YYYY');

SQL SELECT "Item No_",
"Sales Type",
"Sales Code",
"Starting Date",
"Unit Price",
"Ending Date"

Re: Load script data where Date XY

KristianK — Thu, 29 Aug 2019 13:27:41 GMT

Unfortunately no, same Error message

A specific date is working (WHERE "Ending Date"<='01.01.1753') but I need also to show every Price that has Ending Date >=Today

Re: Load script data where Date XY

Anil_Babu_Samineni — Thu, 29 Aug 2019 13:30:23 GMT

If you show that error, That means you are writing in SQL end not the Qlik area. So, Are you not doing preceding load for SQL table? If so can you post full script?

Re: Load script data where Date XY

KristianK — Thu, 29 Aug 2019 13:36:57 GMT

I use the Editor for SQL . This is the whole code:

LIB CONNECT TO 'Nav'

Prices:
LOAD
text("Item No_"),
text("Sales Type"),
text("Sales Code"),
Date("Starting Date",'DD.MM.YYYY'),
"Unit Price" as Cijena,
Date("Ending Date",'DD.MM.YYYY') ;

SQL SELECT "Item No_",
"Sales Type",
"Sales Code",
"Starting Date",
"Unit Price",
"Ending Date"

FROM "XY".dbo."XY$Sales Price" WHERE "Ending Date"<='01.01.1753';

Re: Load script data where Date XY

Anil_Babu_Samineni — Thu, 29 Aug 2019 13:58:21 GMT

I would strongly recommend this?

LIB CONNECT TO 'Nav'

Prices:
LOAD
text("Item No_"),
text("Sales Type"),
text("Sales Code"),
Date("Starting Date",'DD.MM.YYYY'),
"Unit Price" as Cijena,
Date("Ending Date",'DD.MM.YYYY') WHERE "Date("Ending Date",'DD.MM.YYYY') <=Date(Today(),'DD.MM.YYYY');

SQL SELECT "Item No_",
"Sales Type",
"Sales Code",
"Starting Date",
"Unit Price",
"Ending Date"

FROM "XY".dbo."XY$Sales Price";

If you want in SQL you need to use

DATE_FORMAT("Ending Date",'DD.MM.YYYY');

Re: Load script data where Date XY

KristianK — Fri, 30 Aug 2019 06:22:14 GMT

Thank you very much appreciate the help!