Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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,
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
Tahnk you Peter is perfect, thank you again for your precious help.
Best regards and ciao from Italy 🙂