יום חמישי, 28 ביוני 2012

תחרות אלגוריתמים ל-Big Data


מרכז המצוינות של EMC ישראל מכריז היום על פתיחת תחרות ה- Big Data הראשונה מסוגה בארץ ל-Data Scientists (מדעני נתונים) . התחרות, הנושאת פרס כספי בסך עשרת אלפים דולר, פתוחה לכל קהיליית מדעני הנתונים באמצעות אתר האינטרנט Kaggle.com עד הראשון באוגוסט 2012.

מטרת התחרות, הפתוחה ליחידים, קבוצות (עד חמישה איש) וסטארטאפים, אשר יזם ומוביל מרכז המצוינות, היא להגדיל את המודעות בתעשייה המקומית לתחום ה- Big Data בכלל ולתחום העיסוק של מדעני הנתונים בפרט, וכן לתרום לתעשייה עם פרסומם של אלגוריתמים חדשניים.  EMC ישראל קוראת לכל בעל רקע וניסיון ב-machine learning , מדעי המתמטיקה, הסטטיסטיקה והמחשב, כלכלה ופיסיקה, וכל אחד אחר, לנסות מזלו בפתרון האתגר שיחכה לו באתר.

המתחרים יקבלו בסיס נתונים אמתי מתוך קוד מקור פתוח של מאות אלפי קבצים , ויוצג להם אתגר המבוסס על זיהוי אוטומטי של תכנים, במטרה לפצח את האלגוריתם האידיאלי. ועדת השיפוט אשר תפקח על התחרות ותבחר בזוכה על פי קריטריונים קבועים מראש, כוללת שלושה מומחים בתחום מדע הנתונים ממרכז המצוינות של EMC בישראל ונציג מהאקדמיה בתחום machine learning.

יום רביעי, 6 ביוני 2012

ספירת הזמנות במחיר מינימום לפריט

בפוסט באתר dwh.co.il הועלתה השאלה הבאה:
כיצד להציג טבלה ובה עבור כל מוצר, ספירה של ההזמנות שבהן אותו מוצר נמכר במחיר הנמוך ביותר?
למשל: מוצר "כובע מצחיה" נמכר ב-92 הזמנות במחיר 12 ש"ח, ב-36 הזמנות במחיר 14 ש"ח, ואילו ב-45 הזמנות במחיר 13 ש"ח. הטבלה צריכה להציג עבור מוצר זה את הערך 92, כי זוהי כמות ההזמנות שבהן נמכר המוצר במחיר הנמוך ביותר (12 ש"ח).
הצורך הוא שהחישוב יהיה דינמי, ולכן חישוב בסקריפט איננו מתאים.
הנטייה הטבעית היא לנסות להשתמש בפונקציית Aggr אשר יודעת להתחשב גם באגרגציה של הפריט הנוכחי וגם באגרגציה של פריטים מקבילים. ואולם במקרה זה פונקציה זו לא תועיל, כיוון שאנו לא מחפשים את המחיר אלא את מספר ההזמנות של מחיר זה.
ניסיון לחשוב "מחוץ לקופסה" הביא אותי להפוך את הערכים המרובים למחרוזת טקסט, ובמישור זה להתמודד עם הבעיה.
כמובן, קבעתי את Product בתור מימד, ולאחר מכן הרכבתי את הביטוי אשר יתייחס אל Product.
ראשית, הביטוי צריך להכיר את כלל המחירים שבהם נמכר המוצר בכל ההזמנות (כולל מופעים כפולים בהזמנות שונות). זאת ניתן לבצע בעזרת הפונקציה Concat, אשר יודעת לשרשר ערכים מרובים למחרוזת טקסט בודדת. ולכן, הגדרתי תחילה בתור ביטוי:
Concat(PriceInOrder,'_')
השימוש בקו התחתון הוא כדי שתהיה הפרדה בין כל מחיר ומחיר.
מול המחרוזת הארוכה שהיא תוצאת הביטוי הנ"ל, יש להעמיד את מחיר המינימום שבו נמכר פריט זה, כלומר הביטוי
Min(PriceInOrder)
כעת, מה שנשאר הוא לספור כמה פעמים מופיע הביטוי השני (מחיר המינימום) בתוך הביטוי הראשון (כלל המחירים)!
אם-כן הביטוי המלא הוא:
SubstringCount(Concat(PriceInOrder,'_') & '_', min(PriceInOrder) & '_')
יש לשים לב שבחלק של מחיר המינימום הוספנו קו תחתון, כיוון שאנו רוצים לוודא שישנה התאמה מלאה של המספרים (ולא שתימצא התאמה בין 12 ל-122, למשל). גם לחלק של ה-Concat הוספנו בסופו קו תחתון, וזאת מכיוון שאנו רוצים שהחיפוש יתבצע גם על האבר האחרון ששורשר ב-Concat (והפונקציה עצמה אינה מוסיפה את ה-Delimiter בסוף האיברים אלא רק ביניהם).

זה עובד! הטבלה מציגה עבור כל פריט, את כמות ההזמנות שבהן נמכר הפריט במחיר המינימום.


יום שני, 28 במאי 2012

Sum מול Count ורשומות פיקטיביות עבור גרף קו


איך מחשבים בקליקוויו ספירה של הזמנות / לקוחות וכד'? לכאורה התשובה הפשוטה - Count, או בד"כ Count Distinct, על השדה המזהה. למשל:
 Count(DISTINCT OrderID)

אולם ישנה דרך נוספת לספור - באמצעות Sum. בסקריפט מוסיפים שדה Counter (בכל טבלה רלוונטית), ומכניסים לו את הערך 1. לאחר מכן הנוסחה לספירה תהיה:
Sum(OrderCounter)


לשימוש ב-Sum יש מספר יתרונות על פני Count. ראשית, ב-Count צריך בד"כ להשתמש ב-DISTINCT , מכיוון שהשדה המזהה מהווה שדה מקשר (Key) לטבלאות אחרות, כלומר יש לו יותר מופעים מאשר כמות הרשומות שאנו רוצים לספור. כידוע, Distinct מאט את מהירות החישוב ובכך עלול לגרום לבעיית ביצועים.

שנית, לפעמים נרצה להגדיר רשומות מסוימות כרשומות שאינן לספירה. לרשומות אלו יכול להיות מזהה, ולכן אם נשתמש בספירה של המזהה, נהיה בבעיה. המקרה השכיח הוא יצירת רשימות פיקטיביות עבור גרף קו. נצלול יותר אל הסוגיה:

גרף מסוג קו הוא מצוין כדי להמחיש מגמות על ציר הזמן. אך לפעמים עבור חלק מהתקופות אין נתונים, והדבר גורם לעיוות בגרף.
למשל: גרף מכירות לפי חודשים, כאשר בחודש מרץ, עבור אחד מסוכני המכירות, כלל לא היו מכירות. הגרף שיוצג הוא זה:



ניתן לראות שהקו "מדלג" על חודש מרץ, במקום להציגו כ-0, עם ירידה ואז עלייה. לצופה בגרף נדמה כאילו בין פברואר לאפריל הייתה רק עלייה, מה שכמובן אינו נכון.
הפתרון הוא ליצור רשומה פיקטיבית עבור סוכן זה (ועבור כל סוכן / שדה אחר שבו זה נדרש), וב"סכום" ברשומה הפיקטיבית יהיה ערך 0 (יש לנקות את האפשרות Supress Zero-Values).
אבל ישנה בעיה: שדה "סוכן" נמצא בטבלת ההזמנות, ואילו שדה "סכום" נמצא בטבלת שורות הזמנה! לכן עלינו ליצור רשומה פיקטיבית גם בטבלת ההזמנות - בה יהיה הסוכן, וגם בטבלת שורות ההזמנה - בה יהיה הסכום. ואז, כדי לקשר בין הרשומות, נצטרך לתת להזמנה הפיקטיבית מזהה כלשהו. לא נוכל להשאיר את המזהה ריק.
הגרף יתוקן:

וכאן אנחנו חוזרים לנושא הראשון: כיצד נספור את ההזמנות? בטבלת ההזמנות יש כעת רשומות פיקטיביות, שאנו לא מעוניינים לספור, אבל יש להם מזהה. אם ננסה לספור באמצעות Count, הסכום יכלול גם את הרשומות הפיקטיביות!
שימוש ב-Sum לצורך הספירה, פותר את הבעיה. כאשר יוצרים בסקריפט את השדה OrderCounter ושותלים בו 1 בכל הזמנה, עושים זאת רק בהזמנות האמתיות ולא בהזמנות הפיקטיביות. כתוצאה מכך, ספירת  OrderCounter באמצעות Sum תספור רק את ההזמנות האמתיות.

יום שני, 23 באפריל 2012

סוג שדה משתנה בקליקוויו

קליקוויו הוא פורץ דרך בתחומים רבים. מרבים לדבר על הקישור האסוציאטיבי שלו, על התחקור In-Memory ועל העובדה שהוא משלב ETL ותצוגה בכלי אחד (תכונות שבינתיים אומצו ע"י כלים אחרים ואף נחשבות בחלקן כמיינסטרים העתידי).
אבל ישנם תחומים נוספים שבהם קליקוויו מהווה חידוש. אחד מהם הוא העובדה שקליקוויו תומך באפשרות ששדה מסוים הוא גם מספר וגם טקסט.
זוהי תפיסה מהפכנית, לא רק ביחס לעולם הקשוח של בסיסי-הנתונים, שם הגדרת ה-Data Type חקוקה בסלע, אלא ביחס לעולם התוכנה בכלל. אפילו ב-#C בגרסאותיה המתקדמות (מגרסה 2), הפונקציונליות הנקראת Generics מאפשרת אמנם גמישות בהגדרת סוג המשתנה, אבל הגמישות היא רק ב-Design Time. ב-Run Time ייקבע סוג המשתנה והוא יישאר קבוע למשך כל הריצה.
קליקויו מאחסן כל שדה כ-Collection, כאשר אחד המאפיינים של כל אבר הוא IsNumeric, כלומר האם הערך הבודד בכל שדה הוא מספרי או לא. ארכיטקטורה זאת מאפשרת למפתח ליצור שדות שהם בד"כ מספריים אבל יכולים להכיל גם טקסט, מבלי להפסיד את היכולת להתייחס לערכים המספריים ככאלו. למשל, המיון נשאר מיון מספרי בערכים הללו, ובהתאם לכך, אם יש גרף אשר אחד הצירים שלו בנוי על שדה היברידי שכזה, קליקוויו ידע למיין את המספרים נכון, למרות שישנם גם ערכים טקסטואליים (אלו יופיעו בסוף). כמו-כן ניתן להפעיל על השדה פונקציות מספריות (כגון Sum) - קליקוויו יפעיל את הפונקציה על הערכים המספריים ופשוט יתעלם מערכי הטקסט.
כדי לראות זאת בעיניים, נגדיר בסקריפט את הטבלה הבאה:
Array:
load * Inline [
Value
1
2
3
4
Hello
];
ניצור כפתור אשר יריץ את המאקרו הבא:
sub ShowIsNumeric

set val = ActiveDocument.Fields("Value").GetPossibleValues
for i=0 to val.Count-1
       msgbox val.Item(i).IsNumeric
next

end sub
ניתן לראות שעבור ארבעת המספרים, מוצג True (הערך הוא מספרי), ואילו עבור המילה Hello מוצג False.

יום שני, 26 במרץ 2012

שימוש ב-Power Tools כדי ליצור קובץ טקסט של רישיונות קליקויו

זה לא חידוש שאפשר לבנות מודל המנתח את השימוש בקליקויו, בעזרת קובץ הלוג שמייצר השרת - Sessions_servername.
כידוע, מודל כזה מגיע מוכן - QlikView Server Performance. אבל המודל הזה בסיסי מאוד ואיננו מספק - הן בגלל שאין בו ניתוח מפורט יותר של השימוש (סינונים, לשוניות), אשר אפשרי מגרסה 10 בעזרת הקובץ Audit_servername, והן כי חסרים בו נתונים אדמינסטרטיביים כמו מצאי רישיונות והרשאות על המודלים.
לגבי מצאי הרישיונות - איך ניתן לייצר קובץ טקסט כזה (לאלו משתמשים יש רישיון, ואיזה סוג רישיון), כדי שנוכל לנתח בקליקויו את מצב הרישיונות במודל, ולא רק לצפות בו ב-Management console?
QlikTech הוציאה סדרת כלים שימושיים לאדמיניסטרציה בשם Power Tools for Qlikview. ניתן להוריד אותה מכאן.
אחד הכלים - qv-user-manager, מאפשר בין השאר לשלוח פקודה, שהפלט שלה מייצר רשימה מלאה של הרישיונות - מי המשתמש המורשה, תחילת הרישיון וסוג הרישיון. אנו נוסיף לפקודה אופרטור < ושם הקובץ הרצוי, כדי שהפלט יישמר כקובץ:
התוצאה תהיה כאמור, קובץ המכיל את פירוט רישיונות הקליקויו:
 מכאן נוכל כמובן לנתח אותו בעזרת מודל קליקויו.



יום רביעי, 14 במרץ 2012

מקבילה בקליקוויו ל-row_number() over (partition by

נניח שיש לי במערכת טבלת הזמנות, ולידה טבלת פעולות, שבה מתועדות הפעולות השונות שנעשו בהזמנה. למשל עבור הזמנה מסוימת: בתאריך א' ההזמנה התקבלה, בתאריך ב' היא עברה לגורם מטפל, ובתאריך ג' היא נסגרה.
אני מעוניין למספר כל פעולה בסדר עולה, לפי תאריך השלב, וכמובן - המספור צריך להתחיל מחדש עבור כל הזמנה.
באורקל ניתן לעשות זאת כך:
row_number()  over (partition by OrderID order by ActionDate) as ActionNum

האם ניתן לעשות גם בקליקויו? ובכן, כן. המומחה John Witherspoon פרסם כאן את הקוד הבא (בהתאמות לדוגמה שלנו):
left Join (Actions)
LOAD
       ActionID,
       if(OrderID<>previous(OrderID),1,peek('ActionNum')+1) as ActionNum
RESIDENT 
       ActionID
ORDER BY 
       OrderID, ActionDate
;
הסבר: הקוד ממיין את טבלת הפעולות לפי מספר הזמנה, ואח"כ לפי תאריך הפעולה. ע"פ המיון הזה הוא בודק האם ההזמנה הנוכחית שונה מהזמנה הקודמת. אם כן - כלומר אנחנו בפעולה הראשונה של ההזמנה, אזי יוגדר המספר 1. אחרת - כלומר אנחנו בפעולה השנייה והלאה, יוגדר המספר מהשורה הקודמת + 1.

אגב, ניתן להתקדם שלב נוסף, ועל בסיס השדה החדש ActionNum לחשב ערך מצטבר כלשהו. נניח שבטבלת פעולות ישנו שדה ActionCost אשר מכיל את העלות הכספית של כל פעולה, ואני מעוניין לחשב עלות מצטברת לאורך הפעולות (עבור כל הזמנה). אמנם בגרפים ניתן להשתמש ב-Accumulate, אבל למשל ב-Trellis זה לא עובד, וכמו"כ לפעמים נרצה להציג זאת באובייקט Tablebox שבו זה לא אפשרי.
ובכן, לאחר שיצרנו את השדה ActionNum, נוכל להוסיף את הקוד הבא:
left Join (Actions)
LOAD
       ActionID,
       ActionCost + if(ActionNum>1, Peek('AccCost',-1),0) as AccCost
RESIDENT 
       ActionID
ORDER BY 
       OrderID, ActionDate
;


יום שבת, 28 בינואר 2012

רשמים מקליקויו 11

השבוע חזרתי מכנס שבו הציגו את קליקויו 11 עם מספר יכולות מעניינות.
היכולות העיקריות: 
 קליינט WEB  משופר
מבחינתי - אולי היכולת החשובה ביותר. נכון שלא מדובר בפיצ'ר חדש אלא רק בשדרוג טכנולוגיה, אבל בעולם של היום, יכולת WEB-ית אמתית היא עניין הכרחי. לטעמי, הגרסאות שעד 11 לא היו מספיק טובות בתחום זה - מאקרו'ס לא תמיד רצים, הבדלים מסוימים בתצוגה, ותחושה כללית שזה לא לגמרי דף אינטרנט כמו שהוא אמור להיות. בגרסה 11 אנו מדברים כבר על HTML5, וההתרשמות הראשונית שלי היא שמדובר בקפיצת מדרגה משמעותית. 
 ניתן לשתף סשן בין משתמשים (מעין WEBEX)
כאן QlikTech די מקדימים את זמנם. ברור שהיכולת של משתמשים בארגון להסתכל יחד על הנתונים ולנהל עליהם שיחה בזמן אמת, היא מהפכנית. לא ברור כמה ארגונים כבר בשלים לכך היום, אבל זה בטח יעזור להגיע לשם.
אפשרות להוסיף הערות על אובייקטים במודל
ממשיך את מגמת הפיצ'ר הקודם - מאפשר דו-שיח בין משתמשים בנוגע למידע, גם אם שיחה אמתית אינה אפשרית. למשל עובד יכול לבצע סינון מסוים על גרף ולהוסיף הערה: "שיפרנו את מהירות הטיפול בתיקים החודש". לאחר מכן המנהל שלו ייכנס, יוכל לשחזר את הסינונים, ואז לכתוב הערה אחרת: "אבל החודש היו הרבה פחות תיקים לטיפול" ולהפנות לסינון אחר. ממבט שטחי נראה שאין אפשרות לייעד את ההערה למשתמש מסוים - נקווה שיפותח בהמשך. 
ניהול סטים של Set Analysis 
בהחלט עשוי להקל את העבודה עם הפונקציונליות החשובה הזאת. 
 יצירת דוחות פשוטים ע"י המשתמש
ממשק שבו המשתמש יכול לבחור שדות רצויים (ממדים ומדדים) וכך להרכיב בעצמו טבלת נתונים פשוטה. אמנם היה אפשרי גם עד עכשיו ע"י מאקרו, אבל תמיד טוב שישנה יכולת מובנית.

יום רביעי, 25 בינואר 2012

קישור בין טבלאות שלא על בסיס שוויון

כידוע, קליקויו מקשר בין טבלאות ע"פ שדות עם שמות זהים. יש לכך יתרון בקלות הפיתוח (לא צריך להגדיר את הקשרים באופן מפורש), אך גם חיסרון - כאשר רוצים לקשר ע"פ אופרטור שונה משוויון.
למשל, נניח שאנחנו רוצים לקשר בין טבלת מוצרים לטבלת סניפים, כך שכל מוצר יקושר לסניף שבו הוא לא קיים (נכון שניתן להציג חיתוך כזה בתצוגה, אך לפעמים ישנו צורך כזה בסקריפט). הקישור הוא על בסיס האופרטור "שונה".
בשאילתת SQL זה כמובן פשוט מאוד - יוצרים JOIN עם סימן "שונה מ-". בקליקויו זה לא אפשרי.
באותו אופן אנו עשויים לרצות לקשר על בסיס "גדול מ" ו"קטן מ", ואופרטורים נוספים.
אמנם בקליקויו קיימת האפשרות של intervalmatch, אך היא מכסה רק חלק מהמקרים - קישור על בסיס טווח ערכים חופף.
ובכן, מי שמכיר בסיסי נתונים היטב, יודע שלמעשה בכל Join, בסיס-הנתונים מבצע תחילה Cross Join, כלומר יוצר את כל הקומבינציות האפשריות בין רשומות שתי הטבלאות, ורק לאחר מכן גוזר מתוך כמות הרשומות (המפלצתית בד"כ) את הרשומות הרצויות, ע"פ משפט השוויון.
אנחנו יכולים להשתמש באותו עיקרון. אמנם בבסיס הנתונים הפעולה מתבצעת באופן פנימי ללא fetch ולכן במהירות עצומה, ואילו אנו נסבול מביצועים איטיים, אבל זה כמובן רק הסקריפט. עם זאת על רשומות רבות זה בד"כ לא יעבוד בגלל חריגת זיכרון.
אם-כן, הדרך היא להביא את הטבלה הראשונה, ואז במשפט Join להביא את הטבלה השנייה, כאשר אין כל שדה משותף ביניהן. לאחר מכן על הטבלה הגדולה שנוצרה, נוכל לבצע את צמצום הרשומות ע"י משפט Where.

קריאה מרשימות שרפוינט

אחת היכולות החזקות בקליקויו היא האפשרות לקרוא בקלות ממגוון סוגים של מקורות נתונים. אחד ממקורות הנתונים האפשריים הינו Web Service.
בארגון שבו עושים שימוש ב-Microsoft Sharepoint, ניתן לנצל יכולת זו לקריאה מרשימות שרפוינט באמצעות Web Service פנימי של שרפוינט, שמאפשר להציג כל רשימה כ-XML.
למי שלא מכיר, שרפוינט היא סביבה Web-ית לשיתוף מידע בארגון (עם הזמן התפתחה גם כתשתית פיתוח לאתרי אינטרנט בכלל), כאשר השימוש הנפוץ הוא שיתוף מסמכים, אולם ניתן ליצור גם רשימות שרפוינט מותאמות אישית בתור מעין מערכת קלה. שרפוינט מאפשר קישור בין רשימות שונות, כך למשל ניתן לנהל רשימה של לקוחות, ואז ברשימת הזמנות ליצור שדה "לקוח" המבוסס על רשימת הלקוחות.
כאמור, לשרפוינט ישנו Web Service אשר מחצין רשימות כ-XML, משם הקליקויו יכול לקרוא.
להלן דוגמה ל-URL:
http://my-sharepoint-server/my-site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={216d6c83-5dbf-48e4-8d69-ace8e833c19b}
כמובן יש לשנות בהתאם את שם השרת, שם האתר (כולל היררכיית אתרים אם ישנה) ומזהה ה-GUID של הרשימה.
בתוך הסקריפט בקליקויו, נלחץ על כפתור Web File, ובתיבת Internet File נדביק את ה-URL. במסך הבא נבחר את סוג הקובץ XML,  ותחת Tables נבחר xml/data/row ונלחץ Finish. בקוד שנוצר בסקריפט ניתן להשאיר את טבלת xml בלבד ולמחוק את שתי הטבלאות האחרות שנוצרות.
מכאן ואילך ניתן להשתמש בנתונים כמו כל נתונים אחרים.

יום שלישי, 13 בדצמבר 2011

פרצת אבטחה חמורה בקליקוויו

לראות ולא להאמין: בקבצי ה-QVW וה-QVD של קליקוויו, נשמר ה-Connection string המלא לבסיס הנתונים, כולל משתמש וסיסמה - בצורה לא מוצפנת.
הפרצה החמורה התגלתה ופורסמה ע"י Luca Jonathan Panetta, בפורום הבינלאומי של קליקויו:
http://community.qlikview.com/thread/40308?start=0&tstart=0
כדי לראות זאת, צריך פשוט לפתוח את הקובץ ב-Notepad ולחפש את המילה password.
דווח שהבעיה תוקנה בגרסה 11, כך שעכשיו יש עוד סיבה טובה לשדרג - ולא לשכוח להריץ שוב את כל המודלים (כולל הישנים שאינם בשימוש), כדי לעדכן את קבצי ה-QVD.
בינתיים מומלץ בחום לוודא שקבצי ה-QVD (וכמובן ה-QVW) מוגנים היטב ואינם נגישים למי שאיננו אמור לדעת את הסיסמה לבסיס הנתונים.

יום שני, 5 בדצמבר 2011

מציאת שדה טקסט ב-DataBase ע"פ ערך, בעזרת QlikView

קורה שאנחנו לא יודעים מהו שם השדה שאנו זקוקים לו ב-DataBase, ולא מצליחים למצוא אותו בעזרת שאילתות על הקטלוג.
אפשר לחפש את השדה ע"פ אחד הערכים שבו (בד"כ בשדה טקסט). ניתן להריץ ב-DataBase פונקציה שרצה על כל הטבלאות ומחפשת בכל שדה טקסט את המחרוזת. פונקציות כאלו מסתובבת ברשת (גם ל-Oracle וגם ל-MS-SQL).
הבעיה היא שהרצת פונקציה דורשת הרשאות כתיבה ל-DataBase - משהו שכדאי להימנע ממנו בתור אנשי BI.
ובכן, ניתן להשתמש ב-QlikView כדי לבצע פעולה דומה.

ראשית, כדי לנטרל הודעות שגיאה שעלולות לצוץ, נגדיר:
set errormode = 0;
ניצור את ההתחברות ל-DataBase, את הסכמה הרצויה, ואת המחרוזת שאנו מעוניינים לחפש:
(connect to db);
set vSchema = 'MySchema';
set vString = 'StringToFind';
נביא את כל הטבלאות וכל השדות:
tabs:
sqltables;

cols:
sqlcolumns;
ניצור טבלת עמודות זמנית, הכוללת רק את הסכמה שלנו, ורק עמודות מסוג טקסט. נצרף לה את סוג הטבלה, ונמחק את הטבלאות הקודמות:
Columns_Temp:
load 
     TABLE_NAME,
     COLUMN_NAME
resident
     cols
where
     TABLE_SCHEMA = '$(vSchema)'
     and DATA_TYPE = 129;

left join
load
     TABLE_NAME,
     TABLE_TYPE
resident
     tabs;

drop tables tabs, cols;

ניצור טבלת עמודות סופית, שבה רק עמודות מטבלאות מסוג "טבלה" (ולא Views וכד'), ונמחק את הטבלה הזמנית:

Columns:
load
     TABLE_NAME,
     COLUMN_NAME
resident
     Columns_Temp
where
     TABLE_TYPE = 'TABLE';

drop table Columns_Temp;

לבסוף, ניצור לולאה שרצה על כל עמודה, ומחפשת בה את המחרוזת:
for x = 0 to NoOfRows('Columns')-1
     let vTable = peek('TABLE_NAME',x, 'Columns');
     let vColumn = peek('COLUMN_NAME',x, 'Columns');
     Results:
     sql select
          $(vColumn) as value,
          '$(vColumn)' as column_name,
          '$(vTable)' as table_name
     from
          $(vSchema).$(vTable)
     where
          $(vColumn) = '$(vString)' ;
next
לאחר ריצת המודל, נקבל בשדות table_name ו-column_name את הטבלה והעמודה (או הטבלאות והעמודות) שבהן נמצא הערך.

יום רביעי, 30 בנובמבר 2011

מי שמציע - משפיע

כבר יותר משנתיים ש-QlikTech מאפשרים לקהל הרחב להציע רעיונות לשיפור QlikView ולדון בהם:
http://community.qlikview.com/ideas
לא כל חברה מאפשרת את זה, ועוד פחות חברות נותנות משוב ברור האם הרעיון אומץ על ידם, או למה הוא נדחה. לא מעט רעיונות כבר התקבלו, וזאת ההזדמנות להשפיע על הגרסאות הבאות של המוצר.

הנה כמה רעיונות שאני הצעתי:
אפשרות להציג בגרף עמודות עם שני ממדים את ערכי הפרטים (בתוך העמודות) וגם את סך-הכול (מחוץ לעמודות)
http://community.qlikview.com/ideas/2119
הצגת לוג מפורט יותר בעת ריצת הסקריפט
http://community.qlikview.com/ideas/2141
אפשרות להקליט מאקרו
http://community.qlikview.com/ideas/2129
פונקציית Null-To-Value (או לחלופין הרחבת פונקציית Alt גם למחרוזת)
http://community.qlikview.com/ideas/2142
העתקה/הדפסה של כל הגרף, ולא רק החלקים המוצגים במסך
http://community.qlikview.com/ideas/2122

יום רביעי, 23 בנובמבר 2011

ליסטבוקס היררכי

לא פעם יש בגיליון הרבה ליסטבוקסים, וזה עמוס מדיי.
אחת האפשרויות היא להשתמש ב"שידת-מגירות", הלא היא ה-Multi-Box. אני לא מת על זה, בעיקר כי אם נבחר יותר מערך אחד, אז לאחר שהמגירות נסגרות, המשתמש לא רואה מה הערכים שהוא בחר.
אפשרות חלופית היא ליצור ליסטבוקס היררכי. למשל במקום ליסטבוקס של מדינות וליסטבוקס של ערים, יהיה ליסטבוקס היררכי אחד המאגד את שניהם.
ניצור בסקריפט שדה המשרשר את המדינה והעיר, עם תו מפריד כלשהו:
Country & '/' & City as CountryAndCity
 ואז בליסטבוקס נסמן את Show as TreeView (ואם התו המפריד שונה מלוכסן - להכניס את התו). זה ייראה כך:
אמנם זה מסדר את המדינות והערים בהיררכיה, אבל הבעיה היא, שכפי שניתן לראות מהצבע האפור, ערכי ה-"אב" - מדינות, לא ניתנים לבחירה. ניתן לבחור רק את ה-"בנים" - הערים.
מה עושים?
הפתרון הוא ליצור בסקריפט טבלה חדשה ובה שני שדות: שדה העיר - כדי לקשר לטבלה הראשית, ושדה נוסף, שמכיל גם ערכים עם הביטוי הנ"ל, אבל גם (באותו שדה!) את המדינות בלבד:
CitiesHierarchy:
load
  City,
  Country & '/' & City as CountryAndCity
Resident
  Cities;
load
  City,
  Country as CountryAndCity
Resident
  Cities;

וכעת הליסטבוקס יאפשר לבחור גם מדינות:
ניתן לעשות זאת גם על היררכיות עם יותר רמות (לי יש ליסטבוקס עם 5 רמות וזה עובד מצוין). אבל זה בפעם אחרת...

שליפת שדה ע"פ ערך מינימלי/מקסימלי בשדה אחר

קורה שאנו רוצים בסקריפט למצוא רשומה עם ערך מינימום או מקסימום של שדה, אך לשלוף שדה אחר של אותה רשומה.
למשל: להוסיף לטבלת לקוחות, את המוצר האחרון שהלקוח קנה. (לא את תאריך הקנייה, אלא את מק"ט המוצר).

ניתן ליצור זאת בסקריפט בקלות, ע"י שימוש בפונקציה SubField:

left join (Customers)
load
  CustomerID,
  subfield(maxstring(date(SaleDate,'YYYY-MM-DD hh:mm:ss') & '>' & ProductID),'>',2) as Last_Product
resident
  Orders
group by
     CustomerID;

הסבר - מפנים הפונקציה החוצה:
ראשית אנו מפרמטים את תאריך המכירה, מיחידת הזמן הגדולה (שנה) ליחידת הזמן הקטנה (שנייה), וזאת כי בהמשך נשתמש בפונקציית maxstring שממיינת באופן טקסטואלי. הפרמוט באופן הזה מבטיח שהתאריכים ימוינו נכון.
את התאריך המפורמט אנחנו משרשרים לתו < ולמק"ט המוצר. בינתיים הערכים נראים כך:
2011-02-14 10:14:33 > 146782
על הביטוי הזה אנו מפעילים פונקציית maxstring. יוצא שלכל לקוח משויך ביטוי של תאריך הרכישה האחרונה שלו, משורשר למק"ט המוצר באותה רכישה.
לבסוף אנו מפעילים פונקציית subfield, ומעבירים לה את הפרמטר < ו-2, כדי שתחלץ מהביטוי הנ"ל את האבר השני שבתוכו, כאשר החלוקה לאברים היא לפי התו <.
קיבלנו עבור כל לקוח, את מק"ט המוצר שהוא קנה אחרון.


הבלוג נפתח!

ברוכים הבאים,
בבלוג זה אשתף רעיונות, פתרונות והגיגים על כלי ה-BI הנהדר, QlikView.
קצת עליי: אני בעל ניסיון של 10 שנים בתחומי ה-DATA וה-BI. כיום מנהל תחום וראש צוות BI במשרד ממשלתי.
תגובות יתקבלו בשמחה.
נתנאל אלירז