topic Re: Mapping in QlikView

Mapping

Thu, 13 Feb 2014 10:46:40 GMT

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

Re: Mapping

Thu, 13 Feb 2014 11:30:47 GMT

how to check data present in other table in qlikview

Re: Mapping

rbecher — Thu, 13 Feb 2014 11:47:07 GMT

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

Re: Mapping

sujeetsingh — Thu, 13 Feb 2014 11:47:15 GMT

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.

Re: Mapping

Thu, 13 Feb 2014 12:01:03 GMT

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

Re: Mapping

Thu, 13 Feb 2014 12:01:46 GMT

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

Re: Mapping

Thu, 13 Feb 2014 12:05:43 GMT

Thanks for replay @Ralf Becher

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

Re: Mapping

rbecher — Thu, 13 Feb 2014 12:25:50 GMT

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

Re: Mapping

Anonymous — Thu, 13 Feb 2014 12:31:10 GMT

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

Re: Mapping

preminqlik — Thu, 13 Feb 2014 12:51:15 GMT

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;

Re: Re: Mapping

preminqlik — Thu, 13 Feb 2014 12:53:04 GMT

PFA

Re: Re: Mapping

rbecher — Thu, 13 Feb 2014 13:03:53 GMT

Most simple variant:

Date_genration:
LOAD DISTINCT 
    Date(Dates,'DD-MM-YYYY') as Dates, 
    'Holiday' as Flag
FROM fest.xlsx
(ooxml, embedded labels, table is Sheet2);

LOAD *, if(WeekDay(Dates)<6, 'Weekday', 'Weekend') as Flag Where Not Exists(Dates);
LOAD
    Date(Date#(20131231,'YYYYMMDD')+RecNo(),'DD-MM-YYYY') AS Dates
AutoGenerate Ceil (YearEnd(Today())) -YearStart(Today());

- Ralf

Re: Mapping

Thu, 13 Feb 2014 13:08:08 GMT

hi @prem u have not used what data i given for state holiday

Re: Mapping

Thu, 13 Feb 2014 13:10:13 GMT

hi Ralf u have not used State holiday data for mapping

Re: Mapping

rbecher — Thu, 13 Feb 2014 13:12:23 GMT

What does it mean?

Re: Mapping

rbecher — Thu, 13 Feb 2014 13:13:26 GMT

Do you need a set of dates by state?

Re: Mapping

Thu, 13 Feb 2014 13:35:34 GMT

yes i have attached state wise holiday list please find excel file for that

i need this data state wise

Re: Mapping

rbecher — Thu, 13 Feb 2014 13:39:23 GMT

But what when Holiday is on two states? Do you need a full set of dates (a calendar) for all states?

Re: Mapping

Thu, 13 Feb 2014 13:45:28 GMT

yes i need complete set of Data of states

consider that holiday according that state

Re: Re: Mapping

rbecher — Thu, 13 Feb 2014 14:06:33 GMT

Here we go:

States:
LOAD DISTINCT 
    State as StateName
FROM fest.xlsx
(ooxml, embedded labels, table is Sheet2);

Let n=NoOfRows('States');

Holiday:
LOAD DISTINCT 
    State & '_' & Date(Dates,'DD-MM-YYYY') as StateHoliday
FROM fest.xlsx
(ooxml, embedded labels, table is Sheet2);

FOR i=0 to $(n)-1
    Let vState = peek('StateName', $(i), 'States');
    
    Dates:
    LOAD Dates, '$(vState)' as State, If(IsNull(Lookup('StateHoliday', 'StateHoliday', '$(vState)' & '_' & Date(Dates,'DD-MM-YYYY'), 'Holiday')), If(WeekDay(Dates)<6, 'Weekday', 'Weekend'), 'Holiday') as Flag;
    LOAD Date(Date#(20131231,'YYYYMMDD')+RecNo(),'DD-MM-YYYY') AS Dates
    AutoGenerate Ceil (YearEnd(Today())) -YearStart(Today());
NEXT

Drop Tables States, Holiday;

- Ralf