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: 
smilingjohn
Specialist
Specialist

Date

Hi All

after succefully runing the Master calendar , when i tried to check the date this is what  i find , I dont match with the existing date ,and also when i see in table view the subset ratio is only

PLz find the attachment .

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Yes. Because I am looking the same excel file of yours from yesterday.

You can see your transaction date format is "DD-MM-YYYY HH:mm:SS TT" (Not DD-MM-YYYY HH:mm:SS ) you missed the AM/PM format. Change it like below

date(date#(TRANSACTION_DATE,'DD-MM-YYYY HH:mm:SS TT'),'DD-MM-YYYY')as Date  ,

Also I could see wrong date format for the column Period name. Change like below

date(date#(PERIOD_NAME,'MM-DD-YYYY'),'MM-YYYY TT')as Pmonth  ,

I guess you wrongly understood the date format by just seeing the cell. Check the formula bar everytime. You can see original date format.

Capture.PNG

View solution in original post

12 Replies
mightyqlikers
Creator III
Creator III

hi,

try date format like

date(date#(datefield,'DD-MM-YYYY HH:mm:SS'),'DD-MM-YYYY')

Regards
$@M

jsingh71
Partner - Specialist
Partner - Specialist

Try

Date(Date#(<Your Date Field>,'Your Date Field Format'), <'Desired Date Format'>)

Ex: Date(Date#(DateField, 'YYYY/MM/DD'), 'MM/DD/YYYY')

If possible share sample application.

tamilarasu
Champion
Champion

Hi John,

Subset Ratio shows you the percentage of values exists in the current table by comparing with the overall distinct values of that field.

Say for example Master calendar table has 100 date values (Date column) and Product table having only 50 date values (Date column) then the Subset Ratio for master calendar is 50% (50/100) and Subset Ratio for Product is 100% (100/100).


I could see your date field  formatted as DD/MM/YYYY hh:mm:ss (Differ from master calendar). Use proper date function that matches with your master calendar.


Hope this helps you.

smilingjohn
Specialist
Specialist
Author

HI Nagraj U r back , thanks for the explanation .

Please find my QVW file , u can see that in the tabular format the date is not matching , If u can please suggest me it will be great .

jonathandienst
Partner - Champion III
Partner - Champion III

Create a date field without the time component, or split it into a date part and a time part during load, like this:

     LOAD ....

          Date(Floor(Date)) As Date,

          Time(Frac(Date)) As Time,

This assumes that the field Date is correctly read as a numeric date value.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
smilingjohn
Specialist
Specialist
Author

Hi Jonathan , If u please look at my QVW file above it will be really helpfull for me ,

I tried ur option but yet iam getting the same thing , when i add all the fields in a table , the master calendar fields dont match they just keep below

tamilarasu
Champion
Champion

Yes. Because I am looking the same excel file of yours from yesterday.

You can see your transaction date format is "DD-MM-YYYY HH:mm:SS TT" (Not DD-MM-YYYY HH:mm:SS ) you missed the AM/PM format. Change it like below

date(date#(TRANSACTION_DATE,'DD-MM-YYYY HH:mm:SS TT'),'DD-MM-YYYY')as Date  ,

Also I could see wrong date format for the column Period name. Change like below

date(date#(PERIOD_NAME,'MM-DD-YYYY'),'MM-YYYY TT')as Pmonth  ,

I guess you wrongly understood the date format by just seeing the cell. Check the formula bar everytime. You can see original date format.

Capture.PNG

smilingjohn
Specialist
Specialist
Author

Hi Nagraj

I followed the procedure u ask me to do , after reloading it , still when u add SBU , date, other fields from master calendar they dont match

jonathandienst
Partner - Champion III
Partner - Champion III

Your field TRANSACTION_DATE is already numeric, so instead of

date(date#(TRANSACTION_DATE,'DD-MM-YYYY HH:mm:SS'),'DD-MM-YYYY')as Date  ,

use this:

Date(Floor(TRANSACTION_DATE))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein