QlikView App Dev

[#1]

I may/maynot have Mar-2015 qvd. I am able to do conditional load like this:

IF (NOT isNull(qvdCreateTime('QVDs\Mar-2015'))) THEN

QUALIFY *;

UNQUALIFY Id;

PreviousMonthData:

LOAD

     Id,

     Name,

     Price AS PreviousMonthPrice,

     MetaData1 AS PreviousMonthMetaData1,

    MetaData2 AS PreviousMonthMetaData2

FROM

[QVDs\Mar-2015.qvd]

(qvd);

UNQUALIFY *;

END IF

This is my second qvd.

CurrentMonthData:

LEFT JOIN (PreviousMonthData)

LOAD

     Id,

     Name,

     Price AS CurrentMonthPrice,

     LastPriceChangedON

     MetaData1 AS CurrentMonthMetaData1,

    MetaData2 AS CurrentMonthMetaData2

FROM

[QVDs\Apr-2015.qvd]

(qvd);

This is my Final Table:

FinalTable:

LOAD *,

          IF (PreviousMonthMetaData1 <> CurrentMonthMetaData1 or PreviousMonthMetaData2 <> CurrentMonthMetaData2,' Changed', 'Not Changed') as Status

Resident PreviousMonthData;

DROP TABLE PreviousMonthData;

The problem here is if my first qvd does not exist, my second qvd load fails as it does not find PreviousMonthData table in the LEFT JOIN sentence. I can write something like this:

if isnull(TableNumber('PreviousMonthData')) then

PreviousMonthData:

LOAD

     Id,

     Name,

     Price AS CurrentMonthPrice,

     LastPriceChangedON

     MetaData1 AS CurrentMonthMetaData1,

    MetaData2 AS CurrentMonthMetaData2

FROM

[QVDs\Apr-2015.qvd]

(qvd);

ELSE

CurrentMonthData:

LEFT JOIN (PreviousMonthData)

LOAD

     Id,

     Name,

     Price AS CurrentMonthPrice,

     LastPriceChangedON

     MetaData1 AS CurrentMonthMetaData1,

    MetaData2 AS CurrentMonthMetaData2

FROM

[QVDs\Apr-2015.qvd]

(qvd);

end if

But this makes it hard to maintain as i have to make changes at 2 places when i add/remove a field.

Also in my final table i have to do the same thing as if there is no PreviousMonthData table then create fake column in the else statement.

Is there a better solution to this?

[amarbogam]

Try this,

QUALIFY *;

UNQUALIFY Id;

IF (NOT isNull(qvdCreateTime('Prv_QVD.qvd'))) THEN

Set a='LEFT JOIN(PreviousMonthData)';

set b='1 as dummy,';

PreviousMonthData:

LOAD Id,

     Name,

     Price,

     MetaData1,

     MetaData2

FROM

[Prv_QVD.qvd]

(qvd);

else

Set a='CurrentMonthData:';

set b='0 as PreviousMonthMetaData1, 0 as PreviousMonthMetaData2,';

UNQUALIFY PreviousMonthMetaData1;

UNQUALIFY PreviousMonthMetaData2;

end if

$(a)

LOAD

$(b)

Id,

     Name,

     Price,

     MetaData1,

     MetaData2

FROM

[Curr_QVD.qvd]

(qvd);

UNQUALIFY *;

[ramoncova06]

you could create a subrutine and call it after defining you table name, that way you only modify it in one single place

I haven't tested it for this scenario but it should work

[maxgro]

IF (NOT isNull(qvdCreateTime('QVDs\Mar-2015'))) THEN

  PreviousMonthData:

  load ....... FROM [QVDs\Mar-2015.qvd] (qvd);

else

  PreviousMonthData:

  load null() as id, null() as ........., null() as PreviousMonthPrice AutoGenerate 0;

ENDIF;

JOIN (PreviousMonthData)

load ...... ....... FROM [QVDs\Apr-2015.qvd] (qvd);

........