topic Re: How do I create variable using table column in QlikView

How do I create variable using table column

Anonymous — Thu, 17 Dec 2015 17:35:26 GMT

Dear team

How do I create variable from

T1:
LOAD * INLINE [
CDate
01/31/2015
02/29/2015
03/31/2015
04/30/2015
05/31/2015
01/31/2015
02/29/2015
03/31/2015
04/30/2015
05/31/2015
01/31/2015
02/29/2015
03/31/2015
04/30/2015
05/31/2015
];

let vDT1 = COB_31012015.qvd;

let vDT1 = COB_28022015.qvd;

let vDT1 = COB_31032015.qvd; and like wise

Thanks for help

Re: How do I create variable using table column

sunny_talwar — Thu, 17 Dec 2015 17:37:14 GMT

All are vDT1 or you forgot to increment them? vDT1, vDT2... and so on?

Re: How do I create variable using table column

sunny_talwar — Thu, 17 Dec 2015 17:41:15 GMT

May be this:

T1:

LOAD * INLINE [

CDate

01/31/2015

02/29/2015

03/31/2015

04/30/2015

05/31/2015

01/31/2015

02/29/2015

03/31/2015

04/30/2015

05/31/2015

01/31/2015

02/29/2015

03/31/2015

04/30/2015

05/31/2015

];

For i = 1 to 15

LET vDT$(i) = Peek('CDate', -$(i), 'T1');

NEXT

Re: How do I create variable using table column

maxgro — Thu, 17 Dec 2015 18:21:25 GMT

or maybe you want to do something in loop on every file?

T1:

LOAD * INLINE [

CDate

01/31/2015

02/29/2015

03/31/2015

04/30/2015

05/31/2015

01/31/2015

02/29/2015

03/31/2015

04/30/2015

05/31/2015

01/31/2015

02/29/2015

03/31/2015

04/30/2015

05/31/2015

];

For i=0 to NoOfRows('T1')-1

LET file = 'COB_' & date(date#(Peek('CDate', $(i), 'T1'), 'MM/DD/YYYY'), 'DDMMYYYY') & '.qvd';

trace ..... elab file $(file) .....;

// read file ? store ?

NEXT;

Re: How do I create variable using table column

Anonymous — Thu, 17 Dec 2015 18:39:50 GMT

Hi Sunny

I appreciate for answer.

Thanks

Satish

Re: How do I create variable using table column

Anonymous — Thu, 17 Dec 2015 18:50:25 GMT

Hi Maxgro

Nice solution

Thanks

Re: How do I create variable using table column

Thu, 17 Dec 2015 19:09:51 GMT

Hi Satish,

Maybe you are looking for something like this (to be added after your load inline) :

I added a load distinct as there is duplicate date.

Load Distinct CDate Resident T1;

qFilesCount:

Load

count(CDate) as qCntDate

Resident T1;

Let qCntFiles = Peek('qCntDate',0,'qFilesCount');

FOR i=0 to $(qCntFiles)-1

Let vDt$(i) = 'COB_' & Date(Date#(Peek('CDate',$(i),'T1'),'MM/DD/YYYY'),'MMDDYYYY') & '.qvd';

Drop tables qFileCount;

Regards

Re: How do I create variable using table column

Anonymous — Thu, 17 Dec 2015 19:48:51 GMT

Hi Maxgro

I am getting unsorted order

I want like Jan, Feb, Mar. Here is uneven order

Thanks

Re: How do I create variable using table column

maxgro — Thu, 17 Dec 2015 20:29:45 GMT

you can find my .qvw and qvd test file in the attachments; to try, put all the files in the same dir

table is sorted by filedate desc, in the input box the variable with the last (more recent) file name

Re: How do I create variable using table column

Anonymous — Thu, 17 Dec 2015 20:37:24 GMT

Hi Gilles

Still the sorting is issue, Thanks for follow up

Re: How do I create variable using table column

Thu, 17 Dec 2015 21:21:58 GMT

Satish,

I don't know what happens with your dates but it is really strange. First, I thnk that February 29, 2015 doesn't exist. In my sample I changed it by 02/28/2015.

This looks to answer your need even I think we can make it simpler.

Load Distinct

CDate

Resident T1;

T2:

Load

RowNo() as No,

CDate as CDat

Resident T1

Order by CDate;

FOR i=0 to NoOfRows('T2')-1

Let vDt$(i) = 'COB_' & Date(Peek('CDat',$(i),'T2'),'MMDDYYYY') ;

Regards

Gilles