topic Re: Save loop into a QVD in QlikView

Save loop into a QVD

Mon, 11 Jul 2016 15:42:38 GMT

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!

Re: Save loop into a QVD

marcus_malinow — Mon, 11 Jul 2016 15:52:47 GMT

Maybe something like this...

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

Re: Save loop into a QVD

Mon, 11 Jul 2016 15:59:52 GMT

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

Re: Save loop into a QVD

marcus_malinow — Tue, 12 Jul 2016 08:13:55 GMT

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

Re: Save loop into a QVD

Tue, 12 Jul 2016 14:40:59 GMT

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!

Re: Save loop into a QVD

marcus_malinow — Tue, 12 Jul 2016 15:27:21 GMT

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