topic Re: loading dates greater than specific value in QlikView

loading dates greater than specific value

marikabi — Wed, 25 Nov 2020 16:16:04 GMT

Hello guys,

I need an help in modifying the loading script.

What I am trying to do is to load only the data starting from 2016.

I have a column called [Period Name] where the dates are in this format : yyyy-mm-dd

For each record, the value in day is 01.

For example, for November 2016 I have : 2016-11-01

I have data also about 2015 and 2014.

How can I modify the script to load only the required data?

The piece of script for this part looks like:

LOAD Account, [Period Name] AS [MM/YY], [EURO NET], [Country] AS COUNTRY
FROM
(ooxml, embedded labels, table is Data)
WHERE (Account='522110') or (Account='522120') or (Account='522210') or (Account='522220') or (Account='522410') ;

Could you please help me?

Many thanks.

Marika

Re: loading dates greater than specific value

eespiritu — Wed, 18 Jan 2017 15:34:43 GMT

Hi,

and year([Period Name] ) = 2016 ;

Regards,

Re: loading dates greater than specific value

sunny_talwar — Wed, 18 Jan 2017 15:38:49 GMT

May be this:

LOAD Account, [Period Name] AS [MM/YY], [EURO NET], [Country] AS COUNTRY
FROM
(ooxml, embedded labels, table is Data)
WHERE Match(Account, '522110', '522120', '522210', '522220', '522410') and Year(Date#([Period Name], 'YYYY-MM-DD')) >= 2016;

or if Period Name is understood as date already, then this

LOAD Account, [Period Name] AS [MM/YY], [EURO NET], [Country] AS COUNTRY

FROM

(ooxml, embedded labels, table is Data)

WHERE Match(Account, '522110', '522120', '522210', '522220', '522410') and Year([Period Name]) >= 2016;

Re: loading dates greater than specific value

marikabi — Wed, 18 Jan 2017 15:39:26 GMT

Hi Enrique,

unfortunately it doesn't work

Should I also modify something in the first part of the script?

Re: loading dates greater than specific value

marikabi — Wed, 18 Jan 2017 15:47:28 GMT

Hi Sunny,

it doesn't work

Using both the codes I still have all the data..

Any other idea?

Re: loading dates greater than specific value

sunny_talwar — Wed, 18 Jan 2017 15:50:26 GMT

Can you run this:

LOAD Account,

[Period Name] AS [MM/YY],

Num([Period Name]) as NumDate,

Year(Date#([Period Name], 'YYYY-MM-DD')) as Year1,

Year([Period Name]) as Year2,

[EURO NET],

[Country] AS COUNTRY

FROM

(ooxml, embedded labels, table is Data)

WHERE Match(Account, '522110', '522120', '522210', '522220', '522410');

and share a table box with 4 fields in it

[MM/YY],

NumDate,

Year1,

Year2

Re: loading dates greater than specific value

eespiritu — Wed, 18 Jan 2017 15:54:39 GMT

Hi,

Probably your date field has not date format, then you should take only the sub string for the year, try this:

where left([Period Name],4) >= 2016

Re: loading dates greater than specific value

MK9885 — Wed, 18 Jan 2017 15:55:27 GMT

If you've master Calendar already then give the varMinDate as 2016-01-01

Ex:

varMinDate = num(date(mid('2016-01-01',1,10 ),'YYYY-MM-DD'));

This can be used in Master Calendar and the dates will generate only from 2016 and data prior to this will not be available for the user.

Re: loading dates greater than specific value

marikabi — Wed, 18 Jan 2017 16:01:42 GMT

This is the table..

Perhaps I do have a problem with the format of the dates in the second excel?

Re: loading dates greater than specific value

marikabi — Wed, 18 Jan 2017 16:02:25 GMT

Hi,

still nothing..

I am not an expert of Qlik, but this is driving me crazy

Re: loading dates greater than specific value

MK9885 — Wed, 18 Jan 2017 16:13:05 GMT

// Date Dimension

// to load Quarters Full Name

QuarterNAME:

LOAD * Inline [

Quarter , QuarterFullName

Q1 ,FIRST

Q2 ,SECOND

Q3 ,THIRD

Q4 ,FOURTH

];

// to create Quarters ie Q1,Q2

QuartersMap:

    MAPPING LOAD

    rowno() as Month,

    'Q' & Ceil (rowno()/3) as Quarter

    AUTOGENERATE (12);



      varMinDate = num(date(mid('2016-01-01',1,10 ),'YYYY-MM-DD'));

//      varMaxDate = num(date('2017-01-10','YYYY-MM-DD'));

      varMaxDate = num(date(today(),'YYYY-MM-DD'));

// Creating a Temporary Calendar



TempCalendar:

    LOAD

                  $(varMinDate) + Iterno()-1 as Num,

                  Date($(varMinDate) + IterNo() - 1) as TempDate

                  AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

// Date Dimension

MasterCalendar:

LOAD

*,

[Quarter Number]-1 as PQ,

if(LastQtrFlag=1 and len(QuarterEnd([Effective Date]))>0,1,0) as lastQtrMnth,

//If(Year<=$(vLast5Year),Year,If(Year>=$(vLast5Year),Year )) as Years,

// If (Year >= $(vLast5Year), Quarter) as Quarter_5Year,

// If (Year >= $(vLast5Year), Month) as Month_5Year,

AutoNumber ([Year Month Num], 'PeriodID') as PeriodID,

Min([Effective Date]) as MINN,

AutoNumber (Year & Quarter, 'QuarterID') as QuarterID;

Load



trim(date(TempDate,'YYYYMMDD')) as [DateID],

date(TempDate,'MM/DD/YYYY') as [Effective Date],

if(month(QuarterEnd(TempDate))=Month(TempDate),1,0) as [LastMnthPerQtr],

    day(TempDate) as Day,

    TempDate as [US Calendar Format],

    date(TempDate,'DD/MM/YYYY') as [UK Calendar Format],

    date(TempDate,'WWWW') as [Full Day Name],

    year(TempDate) as Year,

    inyear(TempDate,today(),0) * -1 as [CY],    // Current Year

    inyear(TempDate,today(),-1) * -1 as [First PY],

    inyear(TempDate,today(),-2) * -1 as [Second PY],

    inyeartodate(TempDate,today(),0) * -1 as [CYTD],

    inyeartodate(TempDate,today(),-1) * -1 as [First PYTD],

    inyeartodate(TempDate,today(),-2) * -1 as [Second PYTD],

      if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,

if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,

if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,

if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,

if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,

if(InMonth(TempDate, today() ,-1), 1, 0) as LastMonthFlag,

if(InMonthToDate(TempDate, today(),-2), 1, 0) as LastQtrLastMonthFlag,

      ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

    Ceil(Month(TempDate)/3) as [Quarter Number],

    quarterName(TempDate) as [Quarter Name],

    yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3) as [Quarter Year],

    inquarter(TempDate,today(),0) * -1 as [CQ], // Current Quarter

    //    if(InQuarter(TempDate, today(),-1), 1, 0) as [PQ1], // Previous Quarter

    inquarter(TempDate,today(),-4) * -1 as [First PQ],

    inquarter(TempDate,today(),-8) * -1 as [Second PQ],

    inquartertodate(TempDate,today(),0) * -1 as [CQTD],

    inquartertodate(TempDate,today(),-4) * -1 as [First PQTD],

    inquartertodate(TempDate,today(),-8) * -1 as [Second PQTD],

    date(monthstart(TempDate),'MM') as [Month Number],

    num(month(TempDate)) as Num_Month,

    month(TempDate) as Month,

    date(monthstart(TempDate),'MMMM') as [Month Full Name],

    monthstart(TempDate) as [Calendar Month Start Date],

    monthend(TempDate) as [Calendar Month End Date],

date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

    week(TempDate) as Week,

    week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],

    week(weekstart(TempDate)) & '-' & Month(TempDate) as [Week Month],

    weekDay(TempDate) as [Week Day],



    If( TempDate > monthstart(addmonths(today(),-11)) and TempDate <= today(),1) as [Rolling 12],

    If( TempDate > monthstart(addmonths(today(),-2)) and TempDate <= today(),1) as [Rolling 3]



Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Drop Table QuarterNAME;

Load the above script and name the Tab as Master Calendar.

and in your source date

SourceDate:

Load

trim(date(MM/YY,'YYYYMMDD')) as [DateID], //////Used as a key to join Master Calendar

Load

......

.........

.............

From....

varMinDate = num(date(mid('2016-01-01',1,10 ),'YYYY-MM-DD')); This will generate dates only from 2016. (if that's what you're looking for?)

If possible can you upload the same data file? And format of dates can be changed as we like.

Re: loading dates greater than specific value

sunny_talwar — Wed, 18 Jan 2017 16:22:36 GMT

Yes something is not right here.... seems like one Excel might have read the Dates as dates, but not the 2nd one.... I think you will need to use Date#() function for the Excel where date is not read and understood as date

Re: loading dates greater than specific value

marikabi — Wed, 18 Jan 2017 17:38:16 GMT

Hi Sunny,

I have checked more times on the excel, but both the files use the "Date" format.

And I am also wondering that if it wasn't the same format, I couldn't compare the costs from the two files in the same periods, right?

Thank you

Re: loading dates greater than specific value

marikabi — Wed, 18 Jan 2017 17:47:44 GMT

Hi All,

thank for your support.

I don't know why, but the only way the year function worked was copying-pasting the dates as value.

This is crazy, but it worked

Re: loading dates greater than specific value

MarcoWedel — Thu, 19 Jan 2017 01:02:26 GMT

please post a small sample of your source excel file(s).

thanks

regards

Marco

Re: loading dates greater than specific value

marikabi — Thu, 19 Jan 2017 08:43:40 GMT

Hi Marco,

this is the file I am talking about:

Account	Period Name	EURO NET	Country
517110	2014-01-01	0.11631	Austria

Of course there are a lot of more columns, but I don't need them for my analysis (we are talking of about 190 columns for 50k rows.. the application would be too heavy).

The column Period name is formatted as date..

Now it is working fine

sorry but I can't post the original excel file ..