topic Re: Relation with date fields in QlikView

Relation with date fields

user-azadi — Sat, 02 Sep 2017 09:37:39 GMT

Hello all

i have two table (Table_voucheritem , Table_Taghvim) that relationed together

with VoucherDate.in data model view , two tables have relation together ,

but due to different format (date) they can't join together correctly.

i want they join together until when i click on VoucherDate's listbox ,

find Equivalent and show persian_date.

Re: Relation with date fields

effinty2112 — Sat, 02 Sep 2017 11:33:05 GMT

Hi Mohammad,

You have the key field VoucherDate formatted differently in your tables. When loading the table Table_voucheritem, format VoucherDate like

Date(Floor(Timestamp(VoucherDate,'DD/MM/YYYY hh:mm:ss')),'DD.MM.YYYY') as VoucherDate

Good luck

Andrew

Re: Relation with date fields

Kushal_Chawda — Sat, 02 Sep 2017 11:38:35 GMT

Covert both the Dates with same format

Table_voucheritem:

LOAD Credit, Debit, GacSubsidiaryAccountRef, date(floor(VoucherDate)) as VoucherDate

FROM Table_VoucherItem.qvd (qvd);

Table_Taghvim:

LOAD QV_DATE_NUM,

date(floor(GREGORIAN_DATE)) as VoucherDate,

PERSIAN_DATE, Year_Shamsi, Month_Shamsi, Day_Shamsi

FROM Miladi2Shamsi.xlsx (ooxml, embedded labels, table is persian_date);

Re: Relation with date fields

user-azadi — Sat, 02 Sep 2017 11:54:49 GMT

Dear Andrew , Thanks a lot... , That worked

Re: Relation with date fields

passionate — Sat, 02 Sep 2017 11:55:56 GMT

Instead of using date field to join two table

use numeric value of Date to join table.

Re: Relation with date fields

effinty2112 — Sat, 02 Sep 2017 12:05:37 GMT

HI Mohammad,

Glad to have been of help!

Andrew

Re: Relation with date fields

user-azadi — Sat, 02 Sep 2017 12:09:59 GMT

Dear Kushal

your script doesn't work ,

however thank you for your help...

Re: Relation with date fields

effinty2112 — Sat, 02 Sep 2017 13:06:04 GMT

Hi Mohammad,

If your satisfied that you have a solution please mark the correct answer.

Kind regards

Andrew

Re: Relation with date fields

user-azadi — Sat, 02 Sep 2017 16:37:26 GMT

Dear Andrew

i have this syntax:

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

SQL

SELECT

Year

,Month

,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

,sum(Credit) - sum(Debit) as mandeh

, Date(Floor(Timestamp(item.VoucherDate,'DD/MM/YYYY hh:mm:ss')),'DD.MM.YYYY') as VoucherDate

FROM

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

when i run script , don't identify Timestamp function in that (because sql haven't Timestamp function)

what must i to do?