Mapping

[#1]

hi all,

PFA

i have holiday Data of calender year state wise, like

Data:

LOAD State,

     HoliDay,

     Date(Dates,'DD-MM-YYYY') as Dates

FROM

then i Created  Dates of year  by following way

Date_genration:

LOAD  Date(Date#(20131231,'YYYYMMDD')+RecNo(),'DD-MM-YYYY') AS Dates

AutoGenerate Ceil (YearEnd(Today())) -YearStart(Today());

then i joined  these two tables

now i want check when their is holiday then which type it is like

first i want to check whether that is holiday or not by comparing with data stored above Data Table if yes

then i want to check its day type like weekday or weekend

suppose that i have holiday on 1st Jan  suppose it Monday  then its should have flag weekday

suppose that i have holiday on 1st Jan  suppose it Sunday  then its should have flag weekend

this way we will get three field in flag Holiday,Weekday,WeekEnd

i have tried but i can't succeed

thanks in Advance

[#2]

how to check data present in other table in qlikview

[rbecher]

Hi,

I would do it this way:

Date_genration:
LOAD
Date(Date#(20131231,'YYYYMMDD')+RecNo(),'DD-MM-YYYY') AS Dates
AutoGenerate Ceil (YearEnd(Today())) -YearStart(Today());

LEFT Join
LOAD DISTINCT 
    Date(Dates,'DD-MM-YYYY') as Dates, 
    1 as Holiday_Flag, 
    if(WeekDay(Dates)<6, 1, Null()) as Weekday_Flag,
    if(WeekDay(Dates)>=6, 1, Null()) as Weekend_Flag
FROM fest.xlsx
(ooxml, embedded labels, table is Sheet2);

- Ralf

[sujeetsingh]

use the table viewer and if possible then try calling that fields of the table in a table box.

Even you data model is having the synthetic keys . You are also not using the mapping function whereas you have already used mapping Load in your script.

[#5]

that data model is wrong  that's why after join i have stopped

i have tried on that data model many ways do do that my flag thing when i have tried last time then the synthetic key is created ,so forget that synthetic key

now tell me how to cross check whether that holiday is present in that Table or not when creating flag dimension

containing holiday ,weekday ,weeend

[#6]

that data model is wrong  that's why after join i have stopped

i have tried on that data model many ways do do that my flag thing when i have tried last time then the synthetic key is created ,so forget that synthetic key

now tell me how to cross check whether that holiday is present in that Table or not when creating flag dimension

containing holiday ,weekday ,weeknd

[#7]

Thanks for replay @Ralf Becher

but I want create only one flag containing  ALL these thing i.e holiday,Weekday,weekend

[rbecher]

Ok, but Holiday is imdependent from weekday. You can have Holiday on weekday or weekend..

[Anonymous]

See if this is any help.  I don't keep holidays in a separate file but create in the script.

http://community.qlik.com/docs/DOC-5741

[preminqlik]

hi

Date_genration:

LOAD

Date(Date#(20131231,'YYYYMMDD')+RecNo(),'DD-MM-YYYY') AS Dates

AutoGenerate Ceil (YearEnd(Today())) -YearStart(Today());

LEFT Join

LOAD DISTINCT

  Date(Dates,'DD-MM-YYYY') as Dates,

  1 as Holiday_Flag,

  if(WeekDay(Dates)<6, 1, Null()) as Weekday_Flag,

  if(WeekDay(Dates)>=6, 1, Null()) as Weekend_Flag

FROM fest.xlsx

(ooxml, embedded labels, table is Sheet2);

final:

Load *,

text(date(Dates,'WWW')) as Dayname,

if((text(date(Dates,'WWW'))='Sat' or text(date(Dates,'WWW'))='Sun') and len(Holiday_Flag)>0,'Weekend',if(

(text(date(Dates,'WWW'))<>'Sat' or text(date(Dates,'WWW'))<>'Sun') and len(Holiday_Flag)>0,'Weekday' )) as HOLIDAY_STATUS

resident Date_genration;

drop table Date_genration;