topic Re: pickup the data in QlikView

pickup the data

dhanu_today — Sun, 24 Nov 2013 12:41:45 GMT

Dear All,

I have a field with the following sample values:

201305

178

201307

201408

201305

200209

201305

We need the data which contains the YYYYMM(format).It means that we have to capture value which contains 6 digits.

Second one is we have to capture only particular data like 201305 only.

Thanks,

Dhanu

Re: pickup the data

swuehl — Sun, 24 Nov 2013 12:46:08 GMT

Maybe like

LOAD Field FROM YourTable WHERE Len(Field) = 6;

I am not sure what you mean with particular data, can you detail this requirement?

Re: pickup the data

dhanu_today — Sun, 24 Nov 2013 15:17:37 GMT

Thanks Swuehl,

As part of my req 1. we have to capture only 6 digit number from that filed then convert that into number into date format. like 201408(YYYYMM)

2.We have to capture only particular 6 digit numbers like 201305(It repeats more times).

Consider the ID field contains some numbers like 0,4,178 and some date formated data like 201305.

O/p :1

ID SAL

201305 9000

201307 10000

201408 120000

201305 140000

200209 150000

201305 130000

Thanks,

Dhanu

Re: pickup the data

swuehl — Sun, 24 Nov 2013 15:29:22 GMT

To convert the 6 digit number to a date (i.e. a monthstart date), you can use interpretation functions like date#():

LOAD

Date#(Field,'YYYYMM') as DateField

FROM YourTable WHERE Len(Field) = 6;

I still understand what you mean with capture particular numbers like 201305, you don't want to get all the other monthstart dates?

Re: pickup the data

dhanu_today — Sun, 24 Nov 2013 15:52:45 GMT

Yes your right...I dont want all other monthstart dates.

Thanks,

Dhanu

Re: pickup the data

dhanu_today — Sun, 24 Nov 2013 16:01:30 GMT

I have applied the below two conditions.

where Len(ID)=6 and ID='201305';

Thanks,

Dhanu

Re: pickup the data

Clever_Anjos — Sun, 24 Nov 2013 22:10:41 GMT

You don´t need to check twice

where ID='201305'; is enough