topic Re: instead of Timestamp sql function in QV Load Script in QlikView

instead of Timestamp sql function in QV Load Script

user-azadi — Sun, 03 Sep 2017 04:53:48 GMT

Hello All...

i have this syntax in sql 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 that in qv script , don't identify Timestamp function (and maybe Date function) .because sql haven't Timestamp function, and maybe Date function)

what must i to do?

Re: instead of Timestamp sql function in QV Load Script

kaushiknsolanki — Sun, 03 Sep 2017 09:03:35 GMT

Hi,

Try this using the preceding load.

Load

Year

,Month

,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

,mandeh,

Date(Floor(item.VoucherDate),'DD/MM/YYYY') as VoucherDate;

SQL

SELECT

Year

,Month

,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

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

,item.VoucherDate

FROM

Regards,

Kaushik Solanki

Re: instead of Timestamp sql function in QV Load Script

rohitraut — Sun, 03 Sep 2017 10:14:04 GMT

Hello ,

Try this

SQL

SELECT

Year

,Month

,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

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

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

FROM

Re: instead of Timestamp sql function in QV Load Script

user-azadi — Sun, 03 Sep 2017 11:30:13 GMT

Dear Rohit

with use To_Char function , show error message in qv script.

Re: instead of Timestamp sql function in QV Load Script

user-azadi — Sun, 03 Sep 2017 11:33:20 GMT

expression of fied "mandeh" is

sum(Credit) - sum(Debit) as mandeh

and in output qv , show zero value.this mean calculate in sql section , don't work in load section.

Re: instead of Timestamp sql function in QV Load Script

rohitraut — Sun, 03 Sep 2017 12:10:45 GMT

What is error message

Re: instead of Timestamp sql function in QV Load Script

user-azadi — Sun, 03 Sep 2017 12:17:55 GMT

Re: instead of Timestamp sql function in QV Load Script

rohitraut — Sun, 03 Sep 2017 12:43:15 GMT

Try this ,

SQL

SELECT

Year

,Month

,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

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

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

FROM

Re: instead of Timestamp sql function in QV Load Script

user-azadi — Sun, 03 Sep 2017 13:01:26 GMT

Re: instead of Timestamp sql function in QV Load Script

rohitraut — Sun, 03 Sep 2017 13:14:45 GMT

SQL

SELECT

Year

,Month

,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

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

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

FROM

Re: instead of Timestamp sql function in QV Load Script

user-azadi — Sun, 03 Sep 2017 13:19:09 GMT

Unfortunately show same message...

Re: instead of Timestamp sql function in QV Load Script

rohitraut — Sun, 03 Sep 2017 13:25:31 GMT

Please provide the sample data....

Re: instead of Timestamp sql function in QV Load Script

Kushal_Chawda — Sun, 03 Sep 2017 15:26:18 GMT

try this

SQL

SELECT

Year

,Month

,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

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

,CONVERT(date, item.VoucherDate) as VoucherDate

FROM