topic Re: For Loop In Qlikview in QlikView

For Loop In Qlikview

rahulsingh12 — Mon, 21 Aug 2017 06:05:51 GMT

hi,

I have a table which has some 50 fields.

Vendor Version Region State Amount

A 1 EAST AP 140

A 2 WEST KA 150

A 3 SOUTH MP 144

AB 1 EAST AP 140

AB 2 WEST KA 150

AB 3 SOUTH MP 144

I wanted to get an output which looks something like this

VENDOR VERSION FIELD OLD NEW

A 1 REGION - EAST

A 2 REGION EAST WEST

A 3 REGION WEST SOUTH

A 1 STATE - AP

AB 1 REGION - EAST and so on..

The old Column is nothing but the previous value of the given Field and New Column is nothing but the current value of the Field.

I could get the desired result using concatenate.I created a table with the needed structure and started concatenating the values for each column A,but this way is only effective if the number of columns is less

If the number of columns in table A increases to 50 or 100 concatenating is not an efficient way of doing it.

So I wanted to create a loop which takes the column name and its value to give the needed result.

Can someone help me with the looping?

Regards

Rahul

Re: For Loop In Qlikview

arvind_patil — Mon, 21 Aug 2017 06:19:17 GMT

Hi Rahul,

You can create loop like Below:

The function you have for converting the string '01/01/2012' into a date is incorrect - the function Date is used for formatting a date field, whilst the function you require, Date#, converts a string to a number.

You could change the line to read:

FOR i=Date#('01/01/2012','DD/MM/YYYY') to $(y)

The suffix in the QVD file name if this worked, would be the integer number for the day (ie. today is 41372), and you probably want it in a YYYYMMDD format.

Try this syntax instead:

let vDays = num#(today() - date#('01/01/2012','DD/MM/YYYY'));

FOR i = 0 to vDays

let vSuffix = Date(today() - vDays + i, 'YYYYMMDD');

TRACE $(i) - $(vSuffix);

NEXT i

You can use $(vSuffix) in the same way as you do $(i) in your code above.

for More Help

For..next ‒ QlikView

THanks,

Arvind Patil

Re: For Loop In Qlikview

vinieme12 — Mon, 21 Aug 2017 06:23:53 GMT

Use crosstable load

The Crosstable Load

Re: For Loop In Qlikview

vinieme12 — Mon, 21 Aug 2017 06:27:09 GMT

Where did you copy paste this from?

It's irrelevant to the query !

Re: For Loop In Qlikview

Anil_Babu_Samineni — Mon, 21 Aug 2017 06:30:01 GMT

May be create some thing like below

Fact:

LOAD Vendor, Version, Region, State, Amount from Fact;

Left Join (Fact)

Load *, <Logic for FIELD> as FIELD;

Left Join (Fact)

Load *, <Logic for OLD> as OLD;

Left Join (Fact)

Load *, <Logic for NEW> as NEW;

// Do here the Generic Key word

Final:

NoConcatenate

Generic Load * Resident Fact;

Drop Table Fact;

Re: For Loop In Qlikview

rahulsingh12 — Mon, 21 Aug 2017 10:21:48 GMT

Hi,

this solution is useful if the number of fields is less but if you have 100 or 200 fields to do the join from then it will be a tedious task to write so many joins.

That is why i was thinking of some loop to loop through fields.

Regards,

Re: For Loop In Qlikview

Anil_Babu_Samineni — Mon, 21 Aug 2017 10:45:12 GMT

+ its.anandrjs‌ may helps you

Re: For Loop In Qlikview

vinieme12 — Mon, 21 Aug 2017 10:47:48 GMT

post a sample that best represents your scenario

Re: For Loop In Qlikview

its_anandrjs — Mon, 21 Aug 2017 10:56:36 GMT

Read some threads on the missing data might be helpful for you.

Regards

Anand

Re: For Loop In Qlikview

Kushal_Chawda — Mon, 21 Aug 2017 11:12:02 GMT

try this

Data:

load * Inline [

Vendor,Version, Region, State, Amount

A, 1, EAST, AP, 140

A , 2, WEST, KA, 150

A, 3, SOUTH, MP, 144

AB, 1, EAST, AP, 140

AB, 2, WEST , KA, 150

AB, 3, SOUTH, MP, 144 ];

New:

CrossTable(Field,Value,3)

LOAD Vendor,

Version,

Amount,

Region,

State

Resident Data;

DROP Table Data;

Final:

LOAD Vendor,

Version,

Amount,

Field,

Value as NEW,

if(Vendor=Previous(Vendor) and Field=Previous(Field),Previous(Value)) as OLD

Resident New

Order by Vendor,Field,Version;

DROP Table New;

Re: For Loop In Qlikview

rahulsingh12 — Mon, 21 Aug 2017 11:34:01 GMT

Hi,

I have used the below code but does not seem to be giving the needed output.

LOAD * INLINE [

version, Vendor, Region, Core, Amount

1, A, North, North-East, 100

2, A, South, South-, 200

3, A, South2, -West, 200

4, A, South2, South-West, 100

5, A, South, South-West, 150

6, A, South1, South1-West, 150

7, A, South1, South1-West, 100

8, A, South1, South1-West1, 100

1, B, North, North-East, 100

2, B, South, South-, 100

3, B, South, -West, 100

4, B, South2, South-West, 100

5, B, South, South-West, 200

6, B, South1, South1-West, 200

7, B, South1, South1-West, 100

8, B, South1, South1-West1, 100

];

New:

CrossTable(Field,Value,3)

LOAD Vendor,

version,

Amount,

Region,

Core

Resident A;

DROP Table A;

Final:

LOAD Vendor,

version,

Field,

Value as NEW,

if(Vendor=Previous(Vendor) and Field=Previous(Field),Previous(Value)) as OLD

Resident New

Order by Vendor,Field,version;

DROP Table New;

Am I missing something?

Regards,

Rahul

Re: For Loop In Qlikview

Kushal_Chawda — Mon, 21 Aug 2017 11:38:37 GMT

Code seems correct to me

what is the output you are getting with this code and what is expected?

Re: For Loop In Qlikview

rahulsingh12 — Mon, 21 Aug 2017 11:42:39 GMT

Hi,

I got the result.

Thanks a lot.

I need to learn about using crosstable.

Anyways thanks!!