Showing diff data

[#1]

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...

[#2]

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

Lukasz

[#3]

Hi Lukasz,

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

[#4]

Can you update the load script?

[abhaysingh]

join would be the best

[maxgro]

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

[#7]

done it! nope it doesnt work!

[#8]

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()

[#9]

hi,

try using this  on the date field expression

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

regards

[Sokkorn]

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