topic Re: Need a logic for this date data? in QlikView

Need a logic for this date data?

Wed, 25 Mar 2015 10:34:50 GMT

Hi Folks,

I have the data from 01/01/2014 to 12/31/2014. here i want to exclude some dates as per my logic.I tried some couple of things but i didnt get any proper output.

I have attached the excel here.i need all the data except of exclude dates.here i want to exclude all the data which is haing less than Flag.

for example i have a LESS THAN flag and it is having the date -7/17/14

i want to exclude all the dates from 01/01/2014 to 07/17/14. i need a data from 07/18/2014.

Plz suggest me with the logic on this.

Re: Need a logic for this date data?

datanibbler — Wed, 25 Mar 2015 10:42:19 GMT

Hi,

seems easy: If you have that LESS THAN flag with the timerange to exclude, that means the date in that flag_field is actually your start_date, so you have to replace 1/1/year with that date.

You can first load your full data_table, then load that comparison_table, then join the two on a suitable field and if you get a LESS THAN date from that comparison_table, then use that, otherwise use 01/01/year as your start_date - you can generate a third field to calculate that.

HTH

Re: Need a logic for this date data?

Wed, 25 Mar 2015 10:50:06 GMT

Thanks DataNibbler,

But here i have two dates with two LESS THAN flag values.when i am trying it is taking only one date range only.

i am using it use of for loop.

Re: Need a logic for this date data?

chematos — Wed, 25 Mar 2015 15:49:29 GMT

You could createa table containing all the dates you want to non include to your data model and use the exists function in your load statement:

Lets say you have a table with all the dates to exclude in a field named Date.

Then, load your fact table:

Fact:

Load * from FactTable

where not exists(Date,Date);

Regards,

Jose

Re: Need a logic for this date data?

Thu, 26 Mar 2015 07:26:44 GMT

Hi,

I have dates table that is having 01/01/2014 to 12/31/2014.

i need the list of exclude dates.Plz send me the logic code for this scenario.

Re: Need a logic for this date data?

ashfaq_haseeb — Thu, 26 Mar 2015 07:32:11 GMT

Hi,

Check if this helps

Calculating Holidays in script

Regards

ASHFAQ

Re: Need a logic for this date data?

sindu_bharathi — Thu, 26 Mar 2015 07:54:56 GMT

Hi Rakesh,

Use the below logic.

LOAD
[Date Exclude]as Dates

FROM
[..\Data\Date range.xlsx]
(ooxml, embedded labels, table is Sheet1)
where wildmatch(Flag, 'Less than');

noconcatenate

Date:
LOAD Dates
FROM
[..\Data\Dates 1-12.xlsx]
(ooxml, embedded labels, table is Sheet1)
where not exists(Dates);

drop table DateRange;

Hope it helps.

Regards,

Sindu