topic Re: Re: Need help??? in QlikView

Need help???

paulwalker — Thu, 12 Jun 2014 18:30:12 GMT

Hi All,

PFA,

I have excel data source,

please see this image,

City,state,zip code, sap no, apr-11,may-11,...... Apr-12 these are my field's

i want convert into one single field date field (apr-11,may-11,...... Apr-12)

Thanks in advance.....

Re: Need help???

lironbaram — Thu, 12 Jun 2014 18:57:25 GMT

attach is an example

all you need to do is a cross table

your table load looks like

CrossTable(Month, Data, 4)

LOAD City,

State,

[Zip Code],

[SAP No],

[40644],

[40674],

[40705],

[40725],

[40756],

[40787],

[40817],

[40848],

[40878],

[40909],

[40940],

[40969],

[41000]

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Need help???

paulwalker — Fri, 13 Jun 2014 02:50:02 GMT

HI Liron,

How to show date format..

like 11/01/2011

Re: Need help???

Anonymous — Fri, 13 Jun 2014 03:33:21 GMT

Try this:

TempSample:

CrossTable(DataMonth, Data, 4)

LOAD City,

State,

[Zip Code],

[SAP No],

[40644],

[40674],

[40705],

[40725],

[40756],

[40787],

[40817],

[40848],

[40878],

[40909],

[40940],

[40969],

[41000]

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

Sample:

LOAD

Date(Left(DataDate,5), 'MMM-YYYY') as MonthYear

Resident TempSample;

Drop Table TempSample;

Drop Field DataDate;

Re: Re: Need help???

Fri, 13 Jun 2014 04:23:15 GMT

I add some code on Liron Bra'am's post and it works.

Use Date(Num#(Month),'DD/MM/YYYY') as Month, Num transfers Month to Number and Date transfers it to date. 'DD/MM/YYYY' indicate the date format, you can change it to other formats as you like.

Thanks.

Alan Lu

Re: Need help???

Fri, 13 Jun 2014 12:07:51 GMT

Hello everyone,

I like to analyse the Target and Current Revenue.Below is the scenario,

Current Revenue per month- 80,000

My target -1,00,000

Travelling Expenses-10,000

Electricity Charges -3000

Other Expenses -1000.

My Questions is that:

I have a Gauge Chart where One end will display the Current Revenue and opposite will display the Target which I want to achieve.i have three slider control in which Travelling expenses amount,Electricity charges amount and Other Expenses amount are assigned.

If i decreases the Expenses in slider, then automatically my Current Revenue have to increases and

should moves to the target.How can i do this.Give me the code to do it.Please help me.

Re: Need help???

Jason_Michaelides — Fri, 13 Jun 2014 12:24:07 GMT

Nirmal,

How is this question related to this post? Looks like you should start a new question.

Jason

Re: Need help???

MarcoWedel — Sat, 14 Jun 2014 02:06:40 GMT

Hi Paul,

one solution could be:

table1:
CrossTable(MonthNameTemp, Value, 4)
LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/547792-110949/Sample.xlsx] (ooxml, embedded labels, table is Sheet1);

Left Join (table1)

LOAD Distinct
  MonthNameTemp,
  MonthName(Num#(MonthNameTemp)) as MonthName
Resident table1;

DROP Field MonthNameTemp;

hope this helps

regards

Marco

Re: Need help???

MarcoWedel — Wed, 09 Jul 2014 13:40:52 GMT

Hi,

please close this thread if there are no further questions.

Thanks

Regards

Marco