topic Re: Creating back end table in QlikView

Creating back end table

bushpalaamarnat — Thu, 19 Apr 2018 09:16:27 GMT

Hello All,

Could you please, based on today's date need to calculate current week , current week -6 and current week + 13.

attaching the out put in the excel file. Also, this need's to be created in back end and store automatically.

Any suggestion, please let me know.

Re: Creating back end table

olivierrobin — Thu, 19 Apr 2018 09:48:15 GMT

hello

you can use the week function week(date)

so week(today()) for the current week

week(today()-6) for past 6

and week(today()+13) for 13 days in the future

Re: Creating back end table

MK_QSL — Thu, 19 Apr 2018 09:48:24 GMT

You can use below in script.

Week(Date) as [Week No]

Re: Creating back end table

bushpalaamarnat — Thu, 19 Apr 2018 10:05:42 GMT

Sir, is there a better way to write, Week(today()-6), Week(today()-5) and so on..

for each week number i need to create a formula.

Regards,

Neelima.

Re: Creating back end table

olivierrobin — Thu, 19 Apr 2018 10:07:45 GMT

apply the function to your date column

Re: Creating back end table

bushpalaamarnat — Thu, 19 Apr 2018 10:10:44 GMT

You can use below in script.

Week(Date) as [Week No]

This make sense, there is no date column we have to create date column based on today's date.

Re: Creating back end table

MK_QSL — Thu, 19 Apr 2018 12:20:06 GMT

Provide input data as sample file

output data as per your requirement

Re: Creating back end table

petter — Thu, 19 Apr 2018 12:27:13 GMT

This is the script I would recommend for you:

LOAD
  S.No,
  Date,
  [Week No],
  Week,
  [Week Representation],
  Dual('W' & If( RelWeek >= 0 , RelWeek+1 , RelWeek), RelWeek) AS [Week Labels];
LOAD 
     S.No, 
     Date, 
     Week(Date) AS [Week No], 
     'W'&Week(Date)&'-'&Right(Year(Date),2) AS Week, 
     If( Week(Date) = Week(Today(1)) , 'Current', If( Week(Date) < Week(Today(1)) , 'History' , 'Future')) AS [Week Representation],
     (Week(Date)-Week(Today(1))) AS RelWeek, 
     'W' & (Week(Date)-Week(Today(1))) AS [Week Labels]
FROM

(ooxml, embedded labels, table is Sheet1)
WHERE IsNum(S.No);  // Necessary for the Excel file you provided

I used the Dual function in QlikView to create a single field with both a text label and a numeric value as you can see in the last two columns: