Save loop into a QVD

[#1]

I am trying to create a loop to include all the date into a qvd.
Can you help me write an if statement where I can say -- If QVD exist then concatenate the next date but i need it to initially create a qvd when the loop first starts--

LET vStartDate = MakeDate(2015,7,30);

LET vEndDate = (TODAY()-1);

Partner:

LOAD DISTINCT

DISTRIBUTOR_ID

FROM [lib://QVD_Transform/T_Partner.qvd](qvd)

WHERE "Software Provider" = 'ABC'

;

FOR vDayNo = vStartDate to vEndDate;

LET vDate = Date(vDayNo, 'YYYY_MM_DD');

Temp_SplitterTransaction:

    Load *

    From [lib://QVD_Extract/DailySplitterExtract/E_SplitterTransaction_$(vDate).qvd](qvd)

    WHERE exists (DISTRIBUTOR_ID, P_ID);    --- WHERE P_ID in the Temp_SplitterTransaction is included in the DISTRIBUTOR_ID​ in Patner correct???

T_TIPS:

    LOAD *

      RESIDENT Temp_SplitterTransaction

  ----- HOW DO I ADD A IF STATEMENT HERE?

CONCATENATE(T_TIPS)

    LOAD *

    FROM [lib://QVD_Transform/T_TipsSplitter.qvd](qvd) WHERE NOT EXISTS([Transaction Date]);

STORE T_TIPS INTO [lib://QVD_Transform/T_TipsSplitter.qvd];

DROP TABLE Temp_SplitterTransaction;

DROP TABLE T_TIPS;

Thanks in advance!

[marcus_malinow]

Maybe something like this...

If Not IsNull(QvdCreateTime('lib://QVD_Transform/T_TipsSplitter.qvd')) then

[#3]

Are you suggesting

If Not IsNull(QvdCreateTime('lib://QVD_Transform/T_TipsSplitter.qvd')) then

STORE T_TIPS INTO [lib://QVD_Transform/T_TipsSplitter.qvd];

else

CONCATENATE(T_TIPS)

    LOAD *

    FROM [lib://QVD_Transform/T_TipsSplitter.qvd](qvd) WHERE NOT EXISTS([Transaction Date]);

I am getting an error when it gets to the 'CONCATENATE(T_TIPS)' line

[marcus_malinow · Accepted solution]

Azmina,

the condition needs to be placed around the CONCATENATE statement:

If Not IsNull(QvdCreateTime('lib://QVD_Transform/T_TipsSplitter.qvd')) then

    CONCATENATE(T_TIPS)

        LOAD *

        FROM [lib://QVD_Transform/T_TipsSplitter.qvd](qvd) WHERE NOT EXISTS([Transaction Date]);

Endif

STORE T_TIPS INTO [lib://QVD_Transform/T_TipsSplitter.qvd];

I'm not too sure about your WHERE condition though, as this will only work if you have a single record for each Transaction Date

Marcus

[#5]

Thank you Marcus!!!

My where statement is where I am having an issue, you are right I am getting one record for each date, do you have a suggestion for that? I can create a new thread if you like. THANKS A LOT FOR ALL THE HELP!

[marcus_malinow]

Hi Azmina,

it should be pretty simple to resolve this.

I'd suggest adding an additional field to your initial T_TIPS load like this:

[Transaction Date] as [Transaction Date Check]

Then in your EXISTS clause

WHERE NOT EXISTS([Transaction Date Check], [Transaction Date])

Finally, before you STORE T_TIPS

DROP FIELD [Transaction Date Check]

If this is unclear, post your entire code listing.

Marcus