topic Re: Load Script change by date in QlikView

Load Script change by date

Fri, 29 Apr 2016 13:17:50 GMT

Greetings,

I am trying to load two different sets of data depending on the date. From the first business day of the month to some date around the 14th of the month, one set of data is used. This is preliminary data. From some date around the 14th of the month to the last business day of the month, different data is used. This is Final data. I would put an update date into a SQL table when the data is updated mid month. That way I can check to see if the final data has been updated.

Is there a way to read the update date and then load the proper set of data?

I am thinking "IF Month(today) is greater than Month(update date) then load the final data, else load the preliminary data.

Thanks,

Frank

Re: Load Script change by date

marcus_sommer — Fri, 29 Apr 2016 13:21:24 GMT

Something like this:

if day(today()) <= 14 then

load * from preliminary;

else

load * from final;

end if

- Marcus

Re: Load Script change by date

florentina_doga — Fri, 29 Apr 2016 13:23:50 GMT

load data and add this

if(day(today()>15,1,0) as flag

Re: Load Script change by date

Mon, 02 May 2016 10:57:22 GMT

I have a second part to this question. Is it possible to check the modification date of a file from the load script?

Re: Load Script change by date

marcus_sommer — Mon, 02 May 2016 11:00:53 GMT

You could query this with the qv-filefunctions like:

filetime('YourFile')

- Marcus

Re: Load Script change by date

Mon, 02 May 2016 12:28:14 GMT

This is what I am trying to do. I don't think I have the correct syntax.

If(filetime('\\Server\working\Inputs\Preliminary.csv') > filetime('\\Server\working\Inputs\Final.txt'),

SELECT *
FROM "MyDB".dbo.Preliminary;
,

SELECT*
FROM "MyDB".dbo.Final;
);

Re: Load Script change by date

marcus_sommer — Mon, 02 May 2016 12:31:26 GMT

Try it in this way:

If filetime('\\Server\working\Inputs\Preliminary.csv') > filetime('\\Server\working\Inputs\Final.txt') then

SELECT * FROM "MyDB".dbo.Preliminary;
else

SELECT * FROM "MyDB".dbo.Final;
end if

- Marcus

Re: Load Script change by date

rubenmarin — Mon, 02 May 2016 12:42:23 GMT

Hi Frank, there are two 'If', in this case you're using 'If' as script control sentence, so you must use the syntaxis provided by Marcus.

- Script control statement: If...then...elseif...endif

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/ScriptControlStatements/script-control-sta…

- Conditional function: In format If(Condition, then, else)

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/ConditionalFunctions/if.htm

Re: Load Script change by date

Mon, 02 May 2016 12:47:13 GMT

Thank you Marcus. I looked at you earlier reply and it worked after using that syntax. I just need to load that into a table. Whenever I put a table name (MyTable:) in front of it, it errors out.

Re: Load Script change by date

rubenmarin — Mon, 02 May 2016 12:51:46 GMT

Hi Frank, try setting the name in each block of the 'IF':

If filetime('\\Server\working\Inputs\Preliminary.csv') > filetime('\\Server\working\Inputs\Final.txt') then

TableName:

SELECT * FROM "MyDB".dbo.Preliminary;
else

TableName:

SELECT * FROM "MyDB".dbo.Final;
end if