Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Collate two fields from Access Database to create daily view/record

Hi,

Please could i ask for your help...

I update my Access Database on a daily basis, so there are multiple duplicated entries (OLE DB)

The measure i am using is the "Project" field (these are our Customers) but the issue i am having when i am adding the "Created" or "Import Date" field, i am receiving a count of all records, when i only would like the overview from yesterday/today entries.

I have tried multiple FX's but nothing seems to work.

Example Project (Customer):

Count({$<Project={"xxx","xxx","xxx"}>}Project)

Examples Created/Import date (Day):

={">=$(=date(today()-1,'DD/MM/YY'))"}>}

= today()-7 and DateField < today(),

Please could I ask for your help.


P.S. I am using Qlik Sense Desktop



17 Replies

Re: Collate two fields from Access Database to create daily view/record

Please post a small qlik sense app with example data that illustrates the problem.

Not applicable

Re: Collate two fields from Access Database to create daily view/record

!Here you go

Re: Collate two fields from Access Database to create daily view/record

Your date fields seem to be timestamps. So if any of the date values matches up with a date in one of the calendars you're lucky. Most won't since they have a time part that the calendar dates don't.

What I think you should do is add one or two fields in the script to calculate the age of the request:

Today() - Floor(Created) as DaysAgoCreated

Today() - Floor([Import Date]) as DaysAgoImported

You can then use those fields in your set analysis expressions:

count({<Project={'value1','value2', ..etc}>*(<DaysAgoCreated={0,1}>+<DaysAgoImported={0,1}>)}Project)

Not applicable

Re: Collate two fields from Access Database to create daily view/record

Hi,

Thank you for your help but please could i ask for your help in re-designing the script to accommodate your suggestion.

Re: Collate two fields from Access Database to create daily view/record

Add these two lines in the preceding load that loads your data:

Today() - Floor(Created) as DaysAgoCreated,

Today() - Floor([Import Date]) as DaysAgoImported

Not applicable

Re: Collate two fields from Access Database to create daily view/record

Hi Gysbert,
Please could you advise on where i place these two lines (Sorry for being so Green)...

Temp: 

Load

----------------

Let varMinCDate = Num(Peek('MinCDate', 0, 'Temp'));

---------------------------------------------------------------------------------------

TempCalendar1: 

LOAD 

--------------------------------------------------------------------------------------------------

MasterCalendar: 

Load 

Not applicable

Re: Collate two fields from Access Database to create daily view/record

Would you like me to upload my version of the Master Calander?

Not applicable

Re: Collate two fields from Access Database to create daily view/record

Would you like me to upload my version of the Master Calendar?

Re: Collate two fields from Access Database to create daily view/record

In the load where you load your source data, not in the calendars you generate.