topic Re: Multiply two tables to fill missing entries with zero value in QlikView

Multiply two tables to fill missing entries with zero value

Thu, 13 Apr 2017 14:49:42 GMT

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

Re: Multiply two tables to fill missing entries with zero value

vishsaggi — Thu, 13 Apr 2017 15:48:24 GMT

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;

Re: Multiply two tables to fill missing entries with zero value

krishnacbe — Thu, 13 Apr 2017 15:54:54 GMT

Hi,

Can you please share some sample Output you are expecting.

Re: Multiply two tables to fill missing entries with zero value

Fri, 14 Apr 2017 18:57:10 GMT

Hi Krishnapriya,

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