topic Re: Question about table visualization in QlikView

Question about table visualization

Tue, 10 Sep 2013 10:14:10 GMT

Hi to all, I'm facing a problem with qlikview, I have a table object that display several rows. These rows contain 3 columns

ID, Description and DateID

ID is a numeric identifier

Descritpiton is a text and DateId is a datetime created with a script found in a forum

UNQUALIFY DateID;

Date_src:

LOAD

$(StartDate) + ROWNO() -1 AS DateId

AUTOGENERATE $(NumOfDays);

Calendar:

LOAD

DateId AS DateId // just in case

,DATE(DateId) // in format defined in your SET DateFormat=, or in your system format

,DAY(DateId) AS Day

,WEEK(DateId) AS Week_S

,YEAR(DateId) & NUM(MONTH(DateId), '00') & NUM(DAY(DateId),'00') AS DateID

,MONTH(DateId) AS Month_S // simple month name; it is dual - numeric and text

,DUAL(MONTH(DateId) & '-' & YEAR(DateId), YEAR(DateId) & NUM(MONTH(DateId), '00')) AS MonthYear // Month-Year format, dual

,YEAR(DateId) AS Year_S

,WEEKDAY(DateId) AS Weekday

,'Q' & CEIL(MONTH(DateId)/3) AS Quarter // in format Q1, Q2, Q3, Q4

,DUAL('Q' & CEIL(MONTH(DateId)/3) & '-' & YEAR(DateId), YEAR(DateId) & CEIL(MONTH(DateId)/3)) AS QtrYear // Qn-Year, dual

RESIDENT Date_src;

DROP TABLE Date_src;

LET NumOfDays = null();

QlikView performs a LEFT OUTER JOIN so in my table object I have all rows in the database but I have several rows with only DateID value and a '-' as ID and Description.

Is it possible to configure the table object to skip these rows containing only '-'

Thank you in advance for your help.

Best regards

Fabrizio

Re: Question about table visualization

Peter_Cammaert — Tue, 10 Sep 2013 10:33:48 GMT

Yes it is possible to only keep the Calendar rows that link to "transactions". Just do a plain LEFT JOIN (instead of a LEFT OUTER JOIN) of the Calendar table to the transactions table and you're done.

But it all depends on whether you want to show a continuous timeline in your graphs, including periods that have nothing to show (e.g. no sales or something like that)

Peter

Re: Question about table visualization

Peter_Cammaert — Tue, 10 Sep 2013 10:39:29 GMT

Sorry, I was talking about correcting your script.

What table object are you using? A Table Box can be forced to drop records by going to Properties->Presentation, selecting ID and Description in turn, and enabling option "Omit Rows where Field is NULL".

Luck,

Peter

Re: Question about table visualization

Tue, 10 Sep 2013 10:39:35 GMT

Dear Peter thank you for your reply, I'd like, if possibile, maintain the join as is and program the table object to show only the rows different than '-' as ID.

Is it possible to perform this task?

If yes con you suggest me a way?

Thank you for your help.

Best regards

Fabrizio

Re: Question about table visualization

Tue, 10 Sep 2013 10:43:12 GMT

Dear Peter my interface is in Italian language, I add a "Tabella" object (I can see only this table).

Perhaps I could use an object similar to a table but with the possibility to remove this "blank" value,

Re: Question about table visualization

Peter_Cammaert — Tue, 10 Sep 2013 10:49:40 GMT

Switched my UI to Italian as well, my explanation indeed applies to object type "Tabella". Just go to Proprietà->Grafico, select ID from Campi and enable the first option below the list box (Ometti le righe quando il campo è NULL).

What a language, sounds like music!

Peter

Re: Question about table visualization

Tue, 10 Sep 2013 10:54:26 GMT

Tahnk you Peter is perfect, thank you again for your precious help.

Best regards and ciao from Italy 🙂