Value Conversion

[rahulsud007]

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     1-Jan-2015

P1     1     1-Jan-2015

P1     1     1-Jan-2015

P1     1     1-Jan-2015

P1     1     2-Jan-2015

P1     1     2-Jan-2015

P1     1     2-Jan-2015

[anbu1984]

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

[rahulsud007]

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

[anbu1984]

Can you post the script you tried

[rahulsud007]

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)

;

[#6]

Hi Rahul,

the attached should work for you, hope that helps

Joe

[anbu1984 · Accepted solution]

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)

[Anonymous]

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

[#9]

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

[Anonymous]

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] ;