Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
kanve_03
Contributor II
Contributor II

conversion of week and Month to Period

Hi i am a date qvd in which i am having

[Week End] and [Month]

week end has  only the weekends

Month Has only start of the month

i want to create a column as Period which has only two values in Month Or Week as in the below image

fff.PNG

it has to be done in scripting level, where i had to use this in Multibox Resource Library

gwassenaarsana.ismailjaganswuehlQlikViewEducation Services ‌‌Layout & Visualizations

10 Replies
petter
Partner - Champion III
Partner - Champion III

You can create a table called Periods like this:

Periods:

LOAD * INLINE [

     Period, %PeriodType

     Week, 7

     Month, 30

];

Then you can associate %PeriodType with other tables to use this as a filter or in a Set Expression.

The PeriodType is put in to have the option of linking as a key by a numeric key and to more efficiently

search by number instead of text in a Set Expression.

mrossoit
Creator II
Creator II

In the same load statement where you defined your date field add this:

if(monthstart(my_date) = date(my_date), 'Month', if(weekday(my_date) = 5 or weekday(my_date) = 6, 'Week')) as Period

Regards

MR

kanve_03
Contributor II
Contributor II
Author

Thank you
but i need a column like below one where ever the week date appears it should point as Week
if its month start it should point it has MOnth

in my data base i am having a data as below

for example

week end        period

4/5/2015           Week

11/5/2015          week

...

01/6/2015          Month

petter
Partner - Champion III
Partner - Champion III

How do you wish to determine how each row is considered week-end or month? The start of a month can be in a week-end so there is an overlap between the two. Then you must have some other logic that could tell whether this is a Week or a Month row... right?

petter
Partner - Champion III
Partner - Champion III

Actually if you [Week End] field contains Nulls if it is not a Week-End and the [Month] contains Nulls if it is not a Month-row this logic should work:

LOAD

....

      If( Not(IsNull([week end])) , 'Week' , If( Not(IsNull([month])) , 'Month' ) ) AS period

....

If the source doesn't have Nulls but empty strings you would have to adjust it to this:

LOAD

....

      If( [week end]='' , 'Week' , [month]='' , 'Month' ) ) AS period

....


I would advise to use the helper table Periods as I suggested earlier and then the logic should be this:

LOAD

....

      If( Not(IsNull([week end])) , 7 , If( Not(IsNull([month])) , 30 ) ) AS %PeriodType

....



kanve_03
Contributor II
Contributor II
Author

date.PNG

the above image is my calender

where

i am having two variables

Let numWeek=num(curWeek);

Let numMonth=num(curMonth);

[GSS Week End] as Week, where [GSS Week End] < '$(curWeek)';

[Month Start] as Month where [Month Start] < '$(curMonth)';

This is the logic
but i dont know how make a column where period = week or month

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

LOAD

*

If(MonthStart(Weekend) = WeekEnd, 'Month', 'Week') AS Period

FROM DataSource;

Hope this helps you.

Regards,

jagan.

kanve_03
Contributor II
Contributor II
Author

Hi its not working can you please suggest another one
i dont want to see the date column
where ever weekly data is there it should be named as week
and monthly data as Month
both week and month should be sitting in a column Period

kanve_03
Contributor II
Contributor II
Author

HI need to create a extra column in calender which has

week(TempDate) As week

Month(TempDate) As Month,

// one more column has Period (which should have only week or month)

as in below screen shot where ever month starts it should be month

if not it should week

Where Date 1 is Week end / week

month.PNG,