Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

!Here you go

Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
Not applicable
Author

Hi,

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

Gysbert_Wassenaar

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

Today() - Floor(Created) as DaysAgoCreated,

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


talk is cheap, supply exceeds demand
Not applicable
Author

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
Author

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

Not applicable
Author

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand