topic Re: Value Conversion in QlikView

Value Conversion

rahulsud007 — Fri, 24 Apr 2015 07:25:45 GMT

Hi guys, I have Data in the following format.

PID Qty Date

P1 5 1-Jan-2015

P1 3 2-Jan-2015.

And I need output as

PID Flag Date

P1 1 1-Jan-2015

P1 1 2-Jan-2015

Re: Value Conversion

anbu1984 — Fri, 24 Apr 2015 07:49:55 GMT

Load *,1 As Flag While IterNo() <= Qty;
Load * Inline [
PID,Qty,Date
P1,5,1-Jan-2015
P1,3,2-Jan-2015] ;

Re: Value Conversion

rahulsud007 — Fri, 24 Apr 2015 10:04:28 GMT

hi Anbu, i have tried it this is the output i am getting for it.

Re: Value Conversion

anbu1984 — Fri, 24 Apr 2015 10:13:01 GMT

Can you post the script you tried

Re: Value Conversion

rahulsud007 — Fri, 24 Apr 2015 10:15:47 GMT

hi this is the script.

Load *, 1 as Flag while Iterno() <= Qty;

LOAD PID,

Qty,

Date(Date#(PDate,'DD/MM/YYYY'),'DD/MM/YYYY') as PDate

FROM

Stock.xlsx

(ooxml, embedded labels, table is old)

;

Re: Value Conversion

Fri, 24 Apr 2015 10:23:20 GMT

Hi Rahul,

the attached should work for you, hope that helps

Joe

Re: Value Conversion

anbu1984 — Fri, 24 Apr 2015 10:26:48 GMT

If you view data thro Data model, then you will see duplicated rows. Table Box will remove the dups. Try adding RowNo()

Load *, RowNo(), 1 as Flag while Iterno() <= Qty;

LOAD PID,

Qty,

Date(Date#(PDate,'DD/MM/YYYY'),'DD/MM/YYYY') as PDate

FROM

Stock.xlsx

(ooxml, embedded labels, table is old)

Re: Value Conversion

Anonymous — Fri, 24 Apr 2015 10:39:17 GMT

the Point is that anbus solution is working well

but the table on the Surface are doing what qlikview is expected to do

the data is summarized and the ouptut you showed is correct

but there is an easy Workaround if you use a straight table

use the script from Joe and add rowno() to it

Test:
LOAD * INLINE [
PID,Qty,Date
P1,5,1-Jan-2015
P1,3,2-Jan-2015
];
Let vRowCount = NoOfRows('Test');
For i = 0 to $(vRowCount)-1
Test2:
Load
Peek('PID',$(i),'Test') As PID
,1 As Flag
,Peek('Date',$(i),'Test') As Date,
rowno()

AutoGenerate Peek('Qty',$(i),'Test');
//AutoGenerate 5;
next i;
drop Table Test;

Then build a straight table with additional rowno as dimension

then goto tab presentation and set rowno to hide!

I attach qvw

Re: Value Conversion

Fri, 24 Apr 2015 10:45:37 GMT

I actually like the solution from abnu better, I would go with that rather than what I put, it's cleaner

Re: Value Conversion

Anonymous — Fri, 24 Apr 2015 10:48:52 GMT

no Problem, just add rowno() to the script from abnu and build a straight table

Load *,1 As Flag,
rowno() While IterNo() <= Qty;
Load * Inline [
PID,Qty,Date
P1,5,1-Jan-2015
P1,3,2-Jan-2015] ;

Re: Value Conversion

rahulsud007 — Fri, 24 Apr 2015 11:25:13 GMT

Hi Anbu, it worked in the back end itself.

Re: Value Conversion

rahulsud007 — Fri, 24 Apr 2015 11:54:27 GMT

Yes Joe, anbu's method was an easy and understandable one.