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: 
Not applicable

Showing diff data

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

11 Replies
Not applicable
Author

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

Lukasz

Not applicable
Author

Hi Lukasz,

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

Not applicable
Author

Can you update the load script?

abhaysingh
Specialist II
Specialist II

join would be the best

maxgro
MVP
MVP

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


Not applicable
Author

done it! nope it doesnt work!

Not applicable
Author

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

Not applicable
Author

hi,

try using this  on the date field expression

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

regards

Sokkorn
Master
Master

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