Question about table visualization

[#1]

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

[Peter_Cammaert]

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

[Peter_Cammaert]

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

[#4]

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

[#5]

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,

[Peter_Cammaert · Accepted solution]

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

[#7]

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

Best regards and ciao from Italy 🙂