topic Re: writing to table within loop in QlikView

writing to table within loop

Mon, 26 Jan 2015 18:09:46 GMT

Hello,

Trying to create a table which is a result of reading two other resident tables, and a lot of conditional inline logic

(greatly simplified logic here)

[TABLE_A]

Field_1

Field_2

[TABLE_B]

Field_3

Field_4

For each Field_1

if condition is satisfied

Let x = Field_2 * Field_3

??? how do I now write x to TABLE_C ? ???

end if

Question: how to write to create TABLE_C ?

any guidance would be appreciated!

Re: writing to table within loop

Clever_Anjos — Mon, 26 Jan 2015 18:17:12 GMT

It would be easier if you could elaborate more your question, maybe posting some real (fake) data.

There´s some features like LOAD AUTOGENERATE, or LOAD WHILE that could help you

Re: writing to table within loop

Clever_Anjos — Mon, 26 Jan 2015 18:18:56 GMT

Useful reading : Loops in the Script

Re: writing to table within loop

MarcoWedel — Mon, 26 Jan 2015 20:21:18 GMT

You should try to describe the data and expected result instead of the solution you have in mind.

Loops of this kind imo should be the last solution to look for.

Hope this helps

Regards

Marco

Re: writing to table within loop

Mon, 26 Jan 2015 21:36:07 GMT

Thanks, I have read this; all the examples seem focused on reading lists of files. I am more intrested in WRITING new tables based on resident tables.

Re: writing to table within loop

Clever_Anjos — Mon, 26 Jan 2015 21:44:25 GMT

Please tell us more about your need, maybe placing some excel files with inputs and results

Re: writing to table within loop

Peter_Cammaert — Mon, 26 Jan 2015 22:19:52 GMT

Replace the "??? how do I now write x to TABLE_C ? ???" line with something like:

TABLE_C:

LOAD x, somemorestuff, evenmorethings

AUTOGENERATE 1;

Each statement will AUTOCONCATENATE a row to the other rows. Of course, the first LOAD will create the table structure..

But as Marco said, loops aren't always the best constructs to obtain a specific result. They are very slow and there may be more elegant solutions available.

Re: writing to table within loop

jagan — Tue, 27 Jan 2015 03:32:09 GMT

Hi,

Try like this

TableA:

LOAD

Field_1

Field_2

FROM TableA;

TableB:

LOAD

Field_3

Field_4

FROM TableB;

TempTableC:

NoConcatenate

LOAD

RESIDENT TableA;

LEFT JOIN(TempTableC)

LOAD

RESIDENT TableB;

TableC:

LOAD

WHERE Flag = 1;

LOAD

Field_1,

Field_2 * Field_3 AS x,

If(Some Condition, 1, 0) AS Flag

RESIDENT TempTableC;

Hope this helps you.

Regards,

Jagan.

Re: writing to table within loop

Tue, 27 Jan 2015 14:43:09 GMT

Hello all, and thanks for the input thus far. I think the advise from clever and marco is valid ("tell me what you are trying to do").

I am trying to identify stock that will hit its expiry date before it is cosumed (based on forecasted sales). If it does expire before being consumed, need to know the qty that is not sellable.

here is a qvw that I mocked up, I beleive it has all the info

required.

SKUs have a forecast assigned to them. Batches are unique to a SKU. Bataches are consumed FEFO.

any assistance / guidance would be most appreciated!

Re: writing to table within loop

Wed, 28 Jan 2015 16:44:12 GMT

Hi Marco, I have posted it below.

Re: writing to table within loop

Wed, 28 Jan 2015 16:45:01 GMT

Hi there, I have posted an update below, with a better description of what I am trying to do..

Re: writing to table within loop

Fri, 30 Jan 2015 03:16:54 GMT

The solution suggested by Jagan works.

Tried the below solution but its not working (Logic similar to this post http://community.qlik.com/thread/79917)

TableA:
LOAD
Field_1
Field_2
FROM TableA;

TableB:
LOAD
Field_3
Field_4
FROM TableB;

TableC:
LOAD
Field_1,
if(Field_1 < somecondition,(Field_2 * Field_3),Field_1)
RESIDENT TableA;

-Qlikuser