topic Showing diff data in QlikView

Showing diff data

Mon, 10 Feb 2014 08:10:05 GMT

Hi to all qlik experts!

I have a simple question and need your help...Let me briefly explain it with sample data..My first table is

DATE_ID , CUSTOMER , COUNT,

YYYYMMDDhh:mm (The date field is string) A 100

YYYYMMDDhh:mm (The date field is string) B 200

YYYYMMDDhh:mm (The date field is string) C 300

My Second Table is.:

DATE_ID , ORDER , COUNT,

YYYYMMDD (The date field is string) A 5

YYYYMMDD(The date field is string) B 8

YYYYMMDD(The date field is string) C 10

My client want to see two tables in a same chart such as line chart based on date_id...They mainly compare two dimension at the same date ranges..We dont wanna go back to database to fix up some date format issues or join troubles...We want to fix it on qlikview...If you give me a hand I would be appreciated in you..

Thanks for collaboration..

PS: If you send me a sample qvd that would be great for me...

Re: Showing diff data

Mon, 10 Feb 2014 08:22:06 GMT

Try using Date#() function with proper format to interpret the input

Lukasz

Re: Showing diff data

Mon, 10 Feb 2014 08:28:11 GMT

Hi Lukasz,

I have tried it but it does not work for the chart.

Re: Showing diff data

Mon, 10 Feb 2014 08:31:26 GMT

Can you update the load script?

Re: Showing diff data

abhaysingh — Mon, 10 Feb 2014 08:35:04 GMT

join would be the best

Re: Showing diff data

maxgro — Mon, 10 Feb 2014 08:35:04 GMT

for first table load it with

date#(left(DATE_ID,8), 'YYYYMMDD') as DATE_ID

for second one

date#(DATE_ID, 'YYYYMMDD') as DATE_ID

Re: Showing diff data

Mon, 10 Feb 2014 08:36:20 GMT

done it! nope it doesnt work!

Re: Showing diff data

Mon, 10 Feb 2014 08:39:35 GMT

Probably you're keeping time part of the column in first table and date part only in second table. Try Ceil() or apply format to substring as Massimo Grossi suggests

Edit: Floor(), not Ceil()

Re: Showing diff data

Mon, 10 Feb 2014 08:39:45 GMT

hi,

try using this on the date field expression

date(floor(date)'yyyy/mm/dd')

regards

Re: Showing diff data

Sokkorn — Mon, 10 Feb 2014 08:44:21 GMT

Hi Mr. Atacan,

Since our date field is string, we need to use Date#() and Date() to translate those string to proper date format. Your first table contain hh:mm, so we need to omit it from Date field for join with second table. Here we use Floor(). Something like this

[Table1]:

Load

Floor(Date#(DATE_ID,'YYYYMMDDhh:mm')) As LinkKey,

Date(Floor(Date#(DATE_ID,'YYYYMMDDhh:mm'))) As Table1.Date,

CUSTOMER,

COUNT As Table1.Count;

SQL SELECT DATE_ID,CUSTOMER, COUNT FROM TableName1;

[Table2]:

Join ([Table1])

Load

Floor(Date#(DATE_ID,'YYYYMMDD')) As LinkKey,

Date(Floor(Date#(DATE_ID,'YYYYMMDD'))) As Table2.Date,

ORDER,

COUNT As Table2.Count;

SQL SELECT DATE_ID,ORDER, COUNT FROM TableName2;

Regards,

Sokkorn

Re: Showing diff data

Mon, 10 Feb 2014 08:54:27 GMT

The Oscar goes to Sokhorn! He is the most effective and helpful guy on this forum...thanks for other valuable replies as well..

Re: Showing diff data

Sokkorn — Mon, 10 Feb 2014 09:05:23 GMT

Thank you so much Mr. Atacan.

Regards,

Sokkorn