Multiply two tables to fill missing entries with zero value

[#1]

Hi Experts,

I am stymied here after a few hours of effort. I need to outer join (more like multiply) two tables.

Table EQUIPDATA contains test data for 6 different equipment with number of faults summarized in a month identified by the REFDATE (this is always the first day of the month).

Table TIMEDATA contains the sequential months (time range) for which the equipment performance is being analyzed. In the test case I am presenting here, the range spans 24 months.

EQUIPDATA has only 16 entries but for a range spanning 24 months and 6 equipment, I need to create a single table (multiplying the two tables) with 6 times 24 (144) entries, filling in the missing entries with 0 value for NUMFAULTS.

I can write a macro in Excel to generate such a multiplied table but it is not an elegant solution. Nor does it make sense with huge data sets.

How can I do this in Qlikview with a join statement? I tried different kinds of joins. Appreciate your help.

Thanks,

Nagendra

[vishsaggi]

Are you looking something like this? Try this Script?

TIMEDATA:

LOAD INDEX, date(REFDATE,'MM/DD/YYYY') as REFDATE

FROM TESTDATA.xlsx

(ooxml, embedded labels, table is TIMEDATA);

OUTER JOIN (TIMEDATA)

LOAD EQUNR, date(REFDATE,'MM/DD/YYYY') as REFDATE,

     NUMFAULTS

FROM TESTDATA.xlsx

(ooxml, embedded labels, table is EQUIPDATA);

NoConcatenate

FINAL:

LOAD INDEX,

     REFDATE,

     IF(Len(TRIM(EQUNR)) = 0, Peek(EQUNR), EQUNR) AS EQUNR,

     IF(Len(TRIM(NUMFAULTS)) = 0, 0, NUMFAULTS) AS NUMFAULTS

Resident TIMEDATA;

DROP TABLE TIMEDATA;

[krishnacbe]

Hi,

Can you please share some sample Output you are expecting.

[#4]

Hi Krishnapriya,

I am attaching the updated Excel file with one more tab (FULLDATA) - the data that is expected.